If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Refer to a worksheet/name using cell contents?
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. |
#2
|
|||
|
|||
Refer to a worksheet/name using cell contents?
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. "Caeres" wrote: 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. |
#3
|
|||
|
|||
Refer to a worksheet/name using cell contents?
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 -- Biff Microsoft Excel MVP "Caeres" wrote in message ... 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. |
#4
|
|||
|
|||
Refer to a worksheet/name using cell contents?
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. |
#5
|
|||
|
|||
Refer to a worksheet/name using cell contents?
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. "Spiky" wrote: 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. |
#6
|
|||
|
|||
Refer to a worksheet/name using cell contents?
You're welcome!
-- Biff Microsoft Excel MVP "Caeres" wrote in message ... 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. "Spiky" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|