Need fucntion to reference cell on worksheet referenced by known n

[JWirt]

I have a list of names in a Worksheet column that are the names of worksheets
in the workbook.

For example, the column of names could start with,

BYXTRAC
GTEWKO
WERFTY
...


This formula comes close:

=(B6)&"!"&"F6"

where B6 is the worksheet name in column B6 and F6 is the cell to be
referenced in that worksheet. For example, B6 in the column list above is
BYXTRAC. So I wnat a formula to be entered in cell C6, say, in the current
worksheet that references cell F6 in the worksheet named "=B6".

The formula above yields BYXTRAC!F6, which is close but there is no equal
sign to make it a reference function. Adding the equals sign in by adding on
"="& gives the correct formula for the cell but it does not function as a
formula. It just functions as a text string.

Thanks.

John Wirt

In the old days there were reference formulas such as =@@(..).

John Wirt
In a column next to these names I want to enter a formula that looks up cell
F6 on the worksheet with the name in the column to the left.

 

[T. Valko]

Try it like this:

=INDIRECT("'"&B6&"'!F6")

 

[JWirt]

I think the answer is something like this:

You can use the INDIRECT function in conjunction with the ADDRESS function.
The ADDRESS function uses row and column numbers to create a string address.
For example, the formula =ADDRESS(5,6) returns the string $F$5, since $F$5 is
the 5th row of column 6. You can use then pass this to INDIRECT to get the
value in cell F5. For example, =INDIRECT(ADDRESS(5,6)) . While this example
may seem trivial, it illustrates a technique that you can use to build more
complicated formulas.


Copied from: http://www.cpearson.com/excel/indirect.htm

 

[Shane Devenshire]

Hi,

If your sheet names only include letter, numbers and no spaces or other
special characters you can use

=INDIRECT(B6&"!F6")

If your sheet have special characters in their names than you will need to
use the single quotes as in Biff's example.