Home |
Search |
Today's Posts |
#1
|
|||
|
|||
dynamic external cell reference
Any help is appreciated... really stuck on this.
I am trying to reference an external cell where the file name changes/corresponds to a particular local cell value. For example: Column B= Username. Cell B2 = [JOE]. I want to get the data from cell N6 in the excel spreadsheet named c:\data[joe].xls I want to use a formula that uses the username in the cell reference... In the above example, I'm trying something like =data'B2'.xls!N6 to try and get =dataJOE.xls!N6. I'm also trying INDIRECT() function... in C2 ="data"&B2&".xls!N6" which returns "datajoe.xls!N6" then in D2 I use =Indirect(C2) but can't get D2 to actually return the value for =dataJOE.xls!N6 Thanks B _________________ An idea is salvation by imagination. - Frank Lloyd Wright |
#2
|
|||
|
|||
Hi!
A couple of things: datajoe.xls!N6 You don't specify the sheet name. The reference to C2 doesn't work because C2 is a formula that returns the value datajoe.xls!N6. Using Indirect requires that the other WB be opened. Try this: =INDIRECT("[data"&B2&".xls]sheet1!N6") Replace sheet1 with the appropriate sheet name. Biff -----Original Message----- Any help is appreciated... really stuck on this. I am trying to reference an external cell where the file name changes/corresponds to a particular local cell value. For example: Column B= Username. Cell B2 = [JOE]. I want to get the data from cell N6 in the excel spreadsheet named c:\data[joe].xls I want to use a formula that uses the username in the cell reference... In the above example, I'm trying something like =data'B2'.xls!N6 to try and get =dataJOE.xls!N6. I'm also trying INDIRECT() function... in C2 ="data"&B2&".xls!N6" which returns "datajoe.xls!N6" then in D2 I use =Indirect(C2) but can't get D2 to actually return the value for =dataJOE.xls!N6 Thanks B _________________ An idea is salvation by imagination. - Frank Lloyd Wright . |
#3
|
|||
|
|||
"Biff" wrote...
A couple of things: datajoe.xls!N6 You don't specify the sheet name. If these were all single worksheet Excel 2.x/3.x workbooks (or later workbooks with a single worksheet with the same name as the base filename without the .xls extension), then datajoe.xls!N6 is a valid external reference. |
#4
|
|||
|
|||
Hi, Harlan:
Would you care to speculate on the probability that either of your conditions is true in February, 2005? Myrna On Fri, 18 Feb 2005 01:50:46 -0800, "Harlan Grove" wrote: "Biff" wrote... A couple of things: datajoe.xls!N6 You don't specify the sheet name. If these were all single worksheet Excel 2.x/3.x workbooks (or later workbooks with a single worksheet with the same name as the base filename without the .xls extension), then datajoe.xls!N6 is a valid external reference. |
#5
|
|||
|
|||
"Myrna Larson" wrote...
Would you care to speculate on the probability that either of your conditions is true in February, 2005? .... Depends on what application is generating the OP's .xls files. If Excel, then maybe 4-to-1 against. If some creaky in-house character mode app (or even Crystal Reports), then 4-to-1 in favor. Just pointing out that the OP's formula *COULD* be syntactically valid. In such cases, better to *ASK* the OP if that's what they really mean than just to assume they screwed up. Believe it or not, barring inconsistencies I assume OPs do know what they're talking about and have no patience with respondents who second guess without seeking confirmation. You prefer to believe OPs are ignoramuses? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Cell Reference Math | Excel Worksheet Functions | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
name of another worksheet in cell for reference | Excel Worksheet Functions |