index/match
 

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

=INDEX([x.xls]BranfordBowen!$A$1:$C$7, MATCH("Discussion
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 is
modify this function to accept a referenced cell's content. The
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 has
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!


--
elrussell
------------------------------------------------------------------------
elrussell's Profile: http://www.excelforum.com/member.php...o&userid=31020
View this thread: http://www.excelforum.com/showthread...hreadid=506894

index/match
 

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" wrote
in message ...

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

=INDEX([x.xls]BranfordBowen!$A$1:$C$7, MATCH("Discussion
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 is
modify this function to accept a referenced cell's content. The
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 has
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!


--
elrussell
------------------------------------------------------------------------
elrussell's Profile:
http://www.excelforum.com/member.php...o&userid=31020
View this thread: http://www.excelforum.com/showthread...hreadid=506894

index/match
 

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!


--
elrussell
------------------------------------------------------------------------
elrussell's Profile: http://www.excelforum.com/member.php...o&userid=31020
View this thread: http://www.excelforum.com/showthread...hreadid=506894

index/match
 

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

"elrussell" wrote
in message ...

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!


--
elrussell
------------------------------------------------------------------------
elrussell's Profile:
http://www.excelforum.com/member.php...o&userid=31020
View this thread: http://www.excelforum.com/showthread...hreadid=506894