Refer to a worksheet/name using cell contents?

[Caeres]

I'm creating a workbook that contains a "Front Page" worksheet, in which the
user inputs tank numbers, and then the worksheet returns specific information
about the tanks specified. For instance, some tanks are cylindrical, and
have a specific and constant volume/inch of depth, where as others are less
conveniently shaped, and instead have specific calibration tables which
provide the volumes at quantized depth levels.

I've created individual worksheets for each of these calibration tables,
with names like "Table 8", "Table 13", etc. What I want is for a user to be
able to input in a cell the tank number, have the workbook check in a
reference worksheet (which contains either the volume/level constant or the
name of the correct calibration table for each tank), and then if ISNUMBER is
false for the cell containing that information, use the contents of the cell
(which will be the appropriate table name) to refer to the actual worksheet
containing that table.

Example: User inputs T-85 (from drop-down menu, to ensure that the user
input actually exists), and a depth of 5 inches. In the volume output cell,
IF() statement checks the cell in the reference worksheet corresponding to
T-85's volume per depth constant/table reference, finds the text "Table 908",
and then goes to the worksheet entitled "Table 908" and retrieves the volume
at 5 inches. I know how to do everything except for using the cell contents
"Table 908" to actually refer to the worksheet named the same thing as the
cell contents.

NOTE: Above, any time I say table, I'm not actually refering to a literal
Excel table, just an aggregation of related and corresponding data.

 

[Caeres]

Ah, sorry, I'm using Excel 2007. The only alternative I see right now is a
nested IF() statement which checks the specific cell contents against the
known calibration tables, but that gets pretty ridiculous with any real
number of calibration tables.

 

[T. Valko]

It's not real clear what you want.

You need to use the INDIRECT function but I can't figure out what it is
you're looking for.

If you have tables on several sheets and use a drop down to select the sheet
names then the tables would all have to be in the exact same location on
each sheet.

A1 = drop down = table 10

=INDIRECT("'"&A1&"'!A10")

That formula will return the value from 'Table 10'!A10

 

[Spiky]

You can use the INDIRECT function to turn text into a reference.
=INDIRECT("Table 908"&"!"&cell_ref)

So, replace "Table 908" with your formula to return that text, and
replace cell_ref with your formula to identify the cells or lookup
that you are doing.

 

[Caeres]

Ah, that's perfect, exactly what I needed. Thanks a bunch to both of you.
The search function in the Help files leaves much to be desired.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Ah, that's perfect, exactly what I needed. Thanks a bunch to both of you.
The search function in the Help files leaves much to be desired.