Reference another worksheet based on a cell value

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

I'm trying to do a VLOOKUP command and would like to use
a cell to specify the sheet name containing the array.
Command would be =VLOOKUP("A",B1!a1:c3,2) where B1 is a
cell in the current sheet that would contain the sheet
name eg:sheet1 and sheet1 would contain the array in
a1:c3. Is it possible to specify a variable sheet name in
the command ?
 
I am in a similar situation. I have 22 columns, near the top of which I have input text entailing what the columns represent.
For example:
"A3" = Apples , "B3" = Bananas , "C3" = Carrots , etc...

Each column is in reference to a different workbook (each with only 1 critical sheet, labeled "Sheet1"). I need to make a formula to reference certain cells in the workbooks with titles corresponding to the column titles here.
Stipulation: The referenced cell coordinates can't be locked in an outside cell (with the use of INDIRECT() to select them), which means that when I copy the final formula in "A10" to the cells "A11 - A15" the reference will need to adjust itself from [Carrots.xls]Sheet1!M20 in "A10" to '[Carrots.xls]Sheet1'!M21 - M25 in "A11 - A15".

The space above the column titles (rows 1 - 2) can be used to determine the target workbook (based on the column titles).

So, if I've lost you at this point, let me attempt a visual aid:
| A | B | C |
3 | Apples | Bananas | Carrots |
4 | "M10" | "M10" | "M10" |
5 | "M11" | "M11" | "M11" |
6 | "M12" | "M12" | "M12" |

For the sake of specifics, the cells of the other workbooks are in quotes"" in the cells where the appropriate formula needs to be. So, the hand-typed formula of "B5" would be "='[Bananas.xls]Sheet1'!M11" . What I'm looking to do here is to use a formula that will adjust to changes I may make to the column titles.
For example: If I change "B3" to "Carrots" and "C3" to "Bananas" , I need the reference of "B5" to change to ""='[Carrots.xls]Sheet1'!M11" .

I hope I haven't left anything out. Forgive me for being all over the place in this post. I would appreciate any help I can get.

Thanks,
Jeremy
 
Hi

hope I understood you correctly. One the one hand you want to change
the cell name based on the entry in row 3 (Bananas, Apples, etc.). One
the other hand you want to change the row reference to column M (M11,
M12, M13).
Try the following as formula for A4:
=INDIRECT("'[" & A$3 & ".xls]Sheet1'!M" & ROW()+6)

HTH
Frank
 
Frank Kabel said:
hope I understood you correctly. One the one hand you want to change
the cell name based on the entry in row 3 (Bananas, Apples, etc.). One
the other hand you want to change the row reference to column M (M11,
M12, M13).
Try the following as formula for A4:
=INDIRECT("'[" & A$3 & ".xls]Sheet1'!M" & ROW()+6)
Click to expand...
....

Caveat: this only works when any workbook listed in row 3 is open.

Quibble: the ROW() call is unnecessary if INDIRECT can be used.

=INDIRECT("'["&A$3&".xls]Sheet1'!R[6]C13",0)
 
Harlan said:
Frank Kabel said:
hope I understood you correctly. One the one hand you want to change
the cell name based on the entry in row 3 (Bananas, Apples, etc.).
One the other hand you want to change the row reference to column M
(M11, M12, M13).
Try the following as formula for A4:
=INDIRECT("'[" & A$3 & ".xls]Sheet1'!M" & ROW()+6)
Click to expand...
Click to expand...
Caveat: this only works when any workbook listed in row 3 is open.
Click to expand...

Harlan is of course rigth. You can use the Add-In MOREFUC.XLL (see
http://longre.free.fr/english/)
The function INDIRECT.EXT will overcome this Excel restriction.
Quibble: the ROW() call is unnecessary if INDIRECT can be used.
Click to expand...
:-)

Frank
 
Back
Top