Referencing Cell Inside Direct Link?
Feb 8, 2013
I am attempting to setup a spreadsheet that summarizes data from a set of other workbooks. The woorkbooks the data comes from is different for each year. However, I would like to have just one summary that can be changed based on the year entered.
In B1 the user enters the year. Say 2012
In the bulk of the spread sheet below the cells refrence
='C:...["&B1&" Pt. Visit Stats.xlsx]Summary'!$B$6
As I am sure many of you can guess I am getting a #REF! error. I just do not see why it shouldn't work.
I do not want to use the indirect function, because the refering workbooks would need to be open to draw data. I am guessing that I will probably need to research the Harland Grove Pull VBA work or more likely include a copy of Morfunc and then use the indirect function. But, I though I would ask here first just in case I am missing something relatively simple.
View 1 Replies
ADVERTISEMENT
Aug 23, 2009
I'm trying to set up a direct link to the internal excel calculator, using a button in VBA. I have the shortcut on my own toolbar, but I have many users that use the workbook, and to get them all to set up is almost impossible, so I am trying to make it simple for them.
I've tried doing it through a hyperlink direct to the calc.exe file, but get a lot of messages popping up (that'll scare a few of them). So thought I would try and find the excel name for it and use VBA, but I cannot find it anywhere.
View 2 Replies
View Related
Dec 31, 2008
I have 4 cells with simple data in them. In another cell, I would like to make a phrase and include all the data from the 4 cells in that phrase.
Example:
="Jackpot: &G2 (&G1) / &G3 = &G4 each"
I already tried this:
="Jackpot: "&G2" ("&G1") / "&G3" = "&G4" each"
Am I missing something? I'm pretty sure this is doable, I just don't know what's missing.
View 9 Replies
View Related
Oct 2, 2008
is it possible inside this link to put a variable reference? for example to have a cell in my excel which in this case would be Report FY09 and if i change it to Repot FY08 the link would be c:Report FY08[PPV REVENUES FY 09 - Actual.xls]PPV Actual'!$C$4:$BB$4 or in another case if i have the name of the sheet like
='Report FY09'!D4
to do it
='Report FY08'!D4
or even the excel file
c:Report FY08[PPV REVENUES FY 09 - Actual.xls]PPV Actual'!$C$4:$BB$4
to do it
c:Report FY08[PPV REVENUES FY 08 - Actual.xls]PPV Actual'!$C$4:$BB$4
View 4 Replies
View Related
Dec 31, 2009
This is probably very simple I just can’t seem to figure out how to add a link field from one spreadsheet to another spreadsheet header.
View 9 Replies
View Related
Nov 9, 2009
I'm trying to use some data from other workbooks in a formula. However, the path to the other workbooks we are using will often change.
I think that the user can enter the path to the other workbook in a cell in this workbook. However, I'm getting errors in my formula and am not sure how to make the formula use the cell to reference the location of the workbook that has the data that we want.
This is what I have, but it's giving me an error.
=Countif(' & 'Instructions'!B9 & Planning'!$I$3:$I$100, "C")
'Instructions'!B9 contains the location of the other workbook in the format \path[filename] Planning'!$I$3:$I$100 is the location of the range in the other workbook.
View 7 Replies
View Related
Apr 2, 2009
The spread sheet I have written reaches a point, where I want the "ENTER key depress" to jump to a cell to the right instead of the cells in the same column lower in the sheet, is there a way to do this?
View 9 Replies
View Related
May 19, 2014
Is there a way to incorporate these 3 boxes into one? So if I enter a figure into any cell, it will calulate it for the other two? Without overwriting the existing formula. I have attached the problem.
problem.xlsx‎
View 5 Replies
View Related
Jul 20, 2012
How I can directly write info from an external application to an Excel sheet
Actually, I'm using a special dll collection to do it, but I would need to buy the 64 version soon. Problem is I use only one function of the entire collection so I want to look on how to create it my self. Basically, it took data from my charting software and write directly to my Excel Worksheet.Cell
View 3 Replies
View Related
Jan 28, 2013
I have a template with formulas calculating a default value, but still allowing the user to override the cells with direct input.
I want to use conditional formatting to highlight any cells that have been overwritten, but can't find a way for Excel to differentiate between a cell with a formula or an inputted constant.
I realize there is a VBA "isFormula" function, but I don't want to have to use VBA for this.
View 7 Replies
View Related
Jun 28, 2013
I have two worksheets, one with detail monthly information and one with YTD information. So let's say the three numbers I want to capture in the YTD sheet are in columns B, G and I on the monthly sheet. January's data might be in B5, G5 and I5. February's data is in B12, G12 and I12 and so on.
On the YTD sheet in cell C2 I link to Monthly!B5 and in C3 I link to Monthly!B12 so cells C2 to C13 on the YTD sheet show the monthly totals from column B on the detail sheet. On the YTD sheet, cells C20 to C31 show the monthly totals from column G on the monthly sheet, so cells G5, G12, etc. And finally, cells C40 to C51 on the YTD sheet show monthly totals form column I on the monthly sheet.
In the past I've always created all these links manually. After creating the links in C2 to C13 on the YTD sheet, is there a way to use a formula in C21 that uses the link in C2 to create a link for G5?
View 6 Replies
View Related
Mar 5, 2008
a formula to direct me to a certain tab.
Example:
I have a cell with a number, say 312260 and a tab with the same number as the name, I need a formula to send me to that tab.
View 9 Replies
View Related
Nov 25, 2005
Is there a way to connect direct to the newsgroup below via OE6:
microsoft.public.excel.worksheetfunctions
It doesn't appear in the newsgroups' list ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
View 13 Replies
View Related
Jan 3, 2013
Is it possible to import data directly from an email into a spreadsheet?
I have a spreadsheet used for tracking status of new tasks. The tasks are entered manually with the details taken from an email in most cases.
To speed up the data entry i am hoping I can create an email template or an excel template kind of form whereby the originator would fill in set fields/cells, send to me and i would import to the next available row/s in the spreadsheet.
View 1 Replies
View Related
Jul 11, 2008
I have a RecordSet that has been built and accessed sequentially with no problems. I now want to access it directly, via it's Primary Key.
I know I can access it as follows :
With MyTable
.MoveFirst
Do Until .EOF
If ![PrimaryKey] = Work_Key Then
MyData = ![Data_To_Extract]
Exit Do
End If
.MoveNext
Loop
End With
As a MainFrame Programmer, I would expect to be able to access that record directly, via it's Primary Key, something like :
Read MyTable Key = Work_Key
MyData = ![Data_To_Extract]
Am I thinking too much like a Mainframe Programmer (that *is* my trade, it's difficult to adjust sometimes !)? Is the With - Do/Loop method the way to go, or is there a Direct Read method I should be using?
View 9 Replies
View Related
Mar 15, 2014
I need to send emails and have the "Direct Replies To" field updated to a different address.
This is my code. I've remarked what I tried, which is NOT working.
VB:
Dim OutApp As Object
Dim OutMail As Object
Dim EmailBody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
[Code] .....
View 3 Replies
View Related
Mar 24, 2014
I want to get the text inside the cell which is to the left of the first blank cell of a column.
I show the problema in the attach imageSin tÃtulo.jpg
View 6 Replies
View Related
Nov 21, 2008
I have this: =LOOKUP(Budget!J3,B11:B42,E11:E42) RESULT: 5%. what I want: =LOOKUP(Budget!J3,B11:B42,(X3)11:E42) RESULT: 5%. I want excel GET the value inside X3 and and go to the other sheet with this variable. X3 = E
View 2 Replies
View Related
Feb 27, 2009
I'm trying to get dropdown in cell. Depending on what is chosen, I then want another dropdown in the same cell of either New or Old for the item previously chosen. Hopefully the example helps because even I'm confused by this.
View 6 Replies
View Related
Aug 2, 2012
I am wondering if you can write me a VBA code for comparing the value inside a cell.
Code:
For i = 1 To 159
If Sheets("sheet1").Cells(i, 1).Value = Sheets("sheet2").Cells(i, 1) then
..............
Else
[Code]....
View 9 Replies
View Related
Nov 13, 2007
Each cell below contain a number and a time + ET. if like say i want to delete the time + ET and leave only the number inside all cell.
1.1425 8:20pm ET ON A1
3.0805 6:10pm ET ON A2
814.30 6:42am ET ON A3
798.70 11:27am ET ON A4
380.00 9:10pm ET ON A5
View 9 Replies
View Related
Feb 21, 2008
After formating I need to multiply by 2 . As you can see my Cells.Value = Selection.Value * 2 is not correct. How can I accomplish this?
With Range("I17:I4000", Range("I" & Rows.Count).End(xlUp)).Offset(, 1)
.Formula = "=ReplaceChar(I17)"
.Value = .Value
Selection.NumberFormat = "0.00"
Cells.Value = Selection.Value * 2
End With
View 9 Replies
View Related
Sep 23, 2009
I'm starting a project where Excel will be used as the main UI for defining a table of data. I'm expecting to define a "template" - xlt i guess - that users can open and save as an xls, without over-writing the template. Users will populate the spreadsheet with a lot of help from user defined functions. I'm just getting started and would like to populate a particular cell with the current date, but only the first time the sheet is opened. I tried checking whether a cell was empty before assigning a value to it, of course this meant recursion!
Also: It might be nice to use the Today() function inside a UDF, but TODAY isn't a member of Application.WorksheetFunction - is there no way to reference TODAY() from within a UDF?
View 2 Replies
View Related
Oct 6, 2011
I have this loop, I get a range of specific cells and then I loop over this collection searching for a value, if this value exist I wanted to move one place to the right and then set a value on that cell, but for some reason when I put this offser(0,1) parameter, the loop became infinite, not sure why the code is doing this:
Code:
For Each c In Worksheets("Parameters").Range("A3:AR3").Cells
adress_start = ""
adress_letter = ""
[Code].....
View 2 Replies
View Related
Nov 23, 2012
I have made a pivot table which I use each month by just updating the source data, however I was trying to have a cell in another workbook say "=A31" but it keeps going "=getpivotdata(...specific name".
My usual practice was to copy the top ten rows of my pivot table into another file but I am trying to erase this step.
View 1 Replies
View Related
Sep 23, 2009
I want to place a thumbnail image inside a cell for each row of my spreadsheet and have those images STAY in that cell. Once I get the images in the cell, they float - if I sort the rows, the images get all mixed up.
View 9 Replies
View Related
Sep 30, 2006
I'm trying to create a simple VBA function in Excel so that I can sort and calculate results based on various conditions. I can get everything to work except changing the color of the cell where the output is placed. Here is what I have so far:
Function nameOfMyFunction(arguments)
If conditions Then
nameOfMyFunction = "No GRP"
ActiveCell.Font.Color = RGB(255,0,0)
End If
I'm sure that its something simple, but I've spent long enough on my own trying to figure it out and nothing seems to work.
View 5 Replies
View Related
Oct 26, 2006
The following bit of code has worked for me but when it goes back for the next b it still finds the address as $j$13. This address does match the criteria I want but what I want the programme to do is move to the next address matching the criteria in the range...
View 4 Replies
View Related
Apr 9, 2009
rearragning text on excel. I believe Macro will be needed to solve this problem so if anyone can help me out, it will be a big help. Thank you.
Example 1:
I need to rearrange
"trace Silt, cm SAND, some- f Gravel" into
"cm SAND, some- f Gravel, trace Silt"
Example 2:
I need to rearrange
"some+ Silt, f SAND, trace- f Gravel" into
"f SAND, some+ Silt, trace- f Gravel"
There are many different cases for this problem. So if there is a way for the computer to recognize the Capitalized word and move it to the front and rest should be arange in order listed below.
Order
and+
and
and-
some+
some
some-
little+
little
little-
trace+
trace
trace-
each term is seprarated by commas.
For example
f SAND, some+ Silt, trace- f Gravel
each color represent one complete sentence. and the order for them should be capitalized funciton first, then the follow the order provided above.
View 11 Replies
View Related
Dec 14, 2012
How can I view hidden characters inside an excel cell. I have an excel file that I receive from our vendors. After verifying the data, I save the file as a tab delimited .txt format. When I open the .txt file I see some data with " " at both ends. i.e. "800 North Ave. Suite A". The thing is I don't see the " " in excel. This tells me that these are non-printing characters.
View 4 Replies
View Related
