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!

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!

Post your SHEETNAME code.
--
Gary's Student


"frustratedwthis" wrote:

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!

Or try without include ! sinal after the spreasheet name

=Indirect(address(9,3,1,1,b7)

regards

"Marcelo" escreveu:

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!

=SHEETNAME(Index,Format,Sheet type)

forgive me if this is not the code...
does this help?


"Gary''s Student" wrote:

Post your SHEETNAME code.
--
Gary's Student


"frustratedwthis" wrote:

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!

this formula worked great....although I do not understand it...
could you please explain?

"Marcelo" wrote:

Or try without include ! sinal after the spreasheet name

=Indirect(address(9,3,1,1,b7)

regards

"Marcelo" escreveu:

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!