Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Looking up a sheet name
I'm trying to find a function that will look at a cell and find the worksheet
that has the same name as the text in that cell and then report back a cell from that sheet ie. if cell A1 is text "worksheet 3" I want the value in cell B1 to be 'worksheet 3'!$D$12 but if the text is "worksheet 7" I want the value in cell B1 to be 'worksheet 7'!$D$12 I then want to be able to copy this formula down a list of 30 or 40 cells. Does a function exist to do this or is it a complicted nesting situation. Thanks for the help mrengineer |
#2
|
|||
|
|||
=INDIRECT(A1&"!b1")
where A1 holds the sheet name and b1 is the cell that you want the data from "mrengineer" wrote in message ... I'm trying to find a function that will look at a cell and find the worksheet that has the same name as the text in that cell and then report back a cell from that sheet ie. if cell A1 is text "worksheet 3" I want the value in cell B1 to be 'worksheet 3'!$D$12 but if the text is "worksheet 7" I want the value in cell B1 to be 'worksheet 7'!$D$12 I then want to be able to copy this formula down a list of 30 or 40 cells. Does a function exist to do this or is it a complicted nesting situation. Thanks for the help mrengineer |
#3
|
|||
|
|||
=INDIRECT("'"&B1&"'!$D$12")
-- HTH RP (remove nothere from the email address if mailing direct) "mrengineer" wrote in message ... I'm trying to find a function that will look at a cell and find the worksheet that has the same name as the text in that cell and then report back a cell from that sheet ie. if cell A1 is text "worksheet 3" I want the value in cell B1 to be 'worksheet 3'!$D$12 but if the text is "worksheet 7" I want the value in cell B1 to be 'worksheet 7'!$D$12 I then want to be able to copy this formula down a list of 30 or 40 cells. Does a function exist to do this or is it a complicted nesting situation. Thanks for the help mrengineer |
#4
|
|||
|
|||
One way
=INDIRECT("'"&A1&"'!D12") make A1 absolute if you always want to refer to A1 =INDIRECT("'"&$A$1&"'!D12") if you want D12 to change to D13, D14 etc when copied down you would need =INDIRECT("'"&A1&"'!"&CELL("address",D12)) -- Regards, Peo Sjoblom "mrengineer" wrote in message ... I'm trying to find a function that will look at a cell and find the worksheet that has the same name as the text in that cell and then report back a cell from that sheet ie. if cell A1 is text "worksheet 3" I want the value in cell B1 to be 'worksheet 3'!$D$12 but if the text is "worksheet 7" I want the value in cell B1 to be 'worksheet 7'!$D$12 I then want to be able to copy this formula down a list of 30 or 40 cells. Does a function exist to do this or is it a complicted nesting situation. Thanks for the help mrengineer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) | |||
Transfer data from sheet to sheet | Excel Discussion (Misc queries) | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) | |||
Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |