Formula to access data on sheet named in Col A (Excel 2003 SP2)

Topic

New Reply

A user would like a formula that will get information from specific cells on the sheet named in Col A and display the value on the summary sheet.
They know how to manually build the formula, but want a generic formula that can be copied down as new sheets are added. I cannot remember how to use the Quotes required to build the formula, but I have seen it on Woody’s (I tried searching…).
If someone has the time, please take a look at the attached workbook (Cleaned up) and show me how to write the formula.

PS (I DID tell them that the merged cells is a VERY bad idea, but they said they have no choice at this time but to use it this way).

Thanks a heap!!!
Chuck

Reply | Quote

Replies

In B2, you can use the formula

=INDIRECT(A2&"!O3")

Format the cell as a date and fill down as far as needed.
In K2, use

=SUM(INDIRECT(A2&"!T35:T39"))

and fill down as far as needed.
Look up INDIRECT in the Excel help for more info.

Reply | Quote

Hans,

THANK YOU!
(actually I tried indirect first but could ot get it to work and the help did not help me)

Thank you for taking the time to help me out AGAIN!
(I just talked to the user -they are joyous)

Chuck

Reply | Quote