Concatenating Filenames

  • Thread starter Thread starter rbrychckn
  • Start date Start date
R

rbrychckn

I have a generic call to match a given A column name to a the same name
in a target worksheet (in its column A), and returning column 23 from
that target worksheet. The target worksheet is different per entry and
the reference to the sheet is captured in it's own column, the S
column.

However, it only seems to work when the target worksheet is open (I get
#REF errors if it is not). Here is the formula which works when both
sheets are open.

=INDEX(INDIRECT(CONCATENATE("'U:\005\Enrollment Scheme\265 Files -
12172003\[",S2,".xls]MASTER'!A2:Z100")),MATCH(A2:A100,INDIRECT(CONCATENATE("'U:\005\Enrollment
Scheme\265 Files - 12172003\[",S2,".xls]MASTER'!A2:A100")),0),23)

Can anyone help me make this work without having all of the worksheets
open? Thanks.
 
Harlan,
Unless I'm completely missing it, I've put this in the cell:

="=INDEX(INDIRECT(CONCATENATE("'U:\005\Enrollment Scheme\265 Files -
12172003\[",S2,".xls]MASTER'!A2:Z100")),MATCH(A2:A100,INDIRECT(CONCATENATE("'U:\005\Enrollment
Scheme\265 Files - 12172003\[",S2,".xls]MASTER'!A2:A100")),0),23)"

Pasted special as text (I don't have a value option), and substituted
the = for = . I don't seem to be able to make this work. Any help
would be appreciated.
 
...
...
="=INDEX(INDIRECT(CONCATENATE("'U:\005\Enrollment Scheme\265 Files -
12172003\[",S2,".xls]MASTER'!A2:Z100")),MATCH(A2:A100,INDIRECT(CONCATENATE("'U:\005\Enrollment
Scheme\265 Files - 12172003\[",S2,".xls]MASTER'!A2:A100")),0),23)"

Pasted special as text (I don't have a value option), and substituted
the = for = . I don't seem to be able to make this work. Any help
would be appreciated.
Click to expand...

If you're using Excel, and you enter the formula above in a worksheet cell, then
copy that cell's result to the Clipboard (Edit > Copy), then issue the menu
command Edit > Paste Special, Excel's Paste Special dialog *will* show a Values
option (as a radio button, using archaic terminology). If you're seeing a list
box showing Text in the Paste Special dialog, it means you didn't copy the cell
containing this formula to the clipboard. Fill in that missing step and the
procedure works.
 
Back
Top