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.

Hi

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


Arvi Laanemets


"Pester" wrote in message
...
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.

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


"Pester" wrote:

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.

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

"Ron Coderre" wrote:

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


"Pester" wrote:

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.

I got it to work. thank you Ron, very helpful

Pedro

"Ron Coderre" wrote:

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


"Pester" wrote:

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.