INDIRECT sheet Names

P

Pester

I understand how to use the INDIRECT Formula but I am getting a #REF error if
the sheet I refer to has a space in between. If My reference is to look for
information from Sheet1 it works, but if it is from Sheet 1 it does not. Any
tips on this? This is critical because all the sheets refer to products and
have spaces in between the name.

thank you.
 
A

Arvi Laanemets

Hi

=SUM(INDIRECT("'" & A1 & "'!A1:A10"))
where A1 contains sheet name


Arvi Laanemets
 
R

Ron Coderre

If a sheet name has a space in it, the reference becomes something like this:

='Sheet 2'!A1 <-note the apostrophes

Consequently, your formula should allow for spaces in a sheet name.
Try this:

For a sheet name in A1

A1: Sheet 2
B1: =INDIRECT("'"&A1&"'!G5")

That formula returns the value in cell G5 on the sheet named: "Sheet 2"

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
P

Pester

Thank you Ron for answering. But I can;t get it to work. I tried the example
you gave me exactly on Sheet 1 and Sheet 2 to reference G5. But it gives me
an error.

Please advice,

thank you

Pester
 
Top