#REF using =INDIRECT...

G

Guest

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!
 
G

Guest

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:
 
G

Guest

Or try without include ! sinal after the spreasheet name

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

regards

"Marcelo" escreveu:
 
G

Guest

=SHEETNAME(Index,Format,Sheet type)

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

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Referencing formula using Indirect 4
Indirect - Multiple Sheets/Cells 5
indirect function with r1c1 style 4
Sumproduct with multiple criteria using indirect formula 4
Indirect ref in array formulas 3
Using INDIRECT & R1C1 Ref style 5
INDIRECT reference to another worksheet in same workbook 4
trying to use Indirect function 5

Top