INDIRECT sheet Names

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" <Pester@discussions.microsoft.com> wrote in message
news:E229E172-3DC3-4838-AC40-279B21332534@microsoft.com...
> 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.