index/match

[elrussell]

Hi - this works just fine for one named sheet in the other workbook:

=INDEX([x.xls]BranfordBowen!$A$1:$C$7, MATCH("Discussio
Questions",[x.xls]BranfordBowen!$A$1:$A$7,), MATCH("(EDTC/560-
MABJ01IJF3)",[x.xls]BranfordBowen!$A$1:$C$1,))

I have one workbook reading another workbook. What I need to do i
modify this function to accept a referenced cell's content. Th
function is currently hand-coded to read a sheet named "BranfordBowen
(for example) but I need it to accept the contents of a cell that ha
the name of another worksheet "FredSmith", for example. The 'local
cell with the name is b121

I know I need to edit this to something such as "[x.xls]"&$B$121 ..
but I am missing something!

Thanks in advance

 

[Biff]

Hi!

In order to do this the other file *MUST* be open. If the other file is not
open you'll get #REF! errors. Still interested?

Biff

 

[elrussell]

Biff - thanks for the response. Yes, both files are open and in the same
folder. The file (x.xls) that I am trying to read "from" was opened
first.

Here is my latest "try" - it might explain better my efforts...

=INDEX("'[x.xls]"&G12&"'!"&$A$1:$C$60, MATCH("Discussion Questions -
week 3","'[x.xls]"&G12&"'!"&$A$1:$A$60,0),
MATCH("(EDTC/560-MABJ01IJF3)","'[x.xls]"&G12&"'!"&$A$1:$C$1,0))

the cell G12 contains BradfordBowen

I am getting a #VALUE error

Thanks again!

 

[Biff]

Why did your formula references change from your original post? <g>

Try this:

=INDEX(INDIRECT("'[x.xls]"&G12&"'!$A$1:$C$60"),MATCH("Discussion Questions -
week
3",INDIRECT("'[x.xls]"&G12&"'!$A$1:$A$60"),0),MATCH("(EDTC/560-MABJ01IJF3)",INDIRECT("'[x.xls]"&G12&"'!$A$1:$C$1"),0))

As soon as file x.xls is closed and the file that contains this formula
calculates, the formula will return #REF!.

Biff