Hi,

I understand that on the cell B7 you have the name of the spreadsheet, so,
include the ! after the name of spreadsheet and the formula should be:
=Indirect($b7&"$c$9")

regards from Brazil
Marcelo

"frustratedwthis" escreveu:

I have a workbook with multiple sheets. Each sheet has an original tab name.
I am trying to put together a summary of all sheets.
In column B (on the summary sheet ) I have used the formula
=SHEETNAME(5,0,1)
to list all the tabs in the order they are in the workbook
In column D (b & c are merged) I am trying to use the following formula to
pull information from a certain cell on each sheet.
=INDIRECT($B7&"!$C$9")
I keep getting a #Ref.
Any ideas on what I am doing wrong or what I should do different?
Any help is GREATLY appreciated!
Thanks in advance!