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 |
=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 |
=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 |
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 |
All times are GMT +1. The time now is 11:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com