software

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(quot;'quot; amp; A1 amp; quot;'!A1:A10quot;))
where A1 contains sheet nameArvi Laanemetsquot;Pesterquot; gt; wrote in message
...
gt; I understand how to use the INDIRECT Formula but I am getting a #REF error
if
gt; the sheet I refer to has a space in between. If My reference is to look
for
gt; information from Sheet1 it works, but if it is from Sheet 1 it does not.
Any
gt; tips on this? This is critical because all the sheets refer to products
and
gt; have spaces in between the name.
gt;
gt; thank you.
If a sheet name has a space in it, the reference becomes something like this:

='Sheet 2'!A1 lt;-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(quot;'quot;amp;A1amp;quot;'!G5quot;)

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

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

XL2002, WinXPquot;Pesterquot; wrote:

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

quot;Ron Coderrequot; wrote:

gt; If a sheet name has a space in it, the reference becomes something like this:
gt;
gt; ='Sheet 2'!A1 lt;-note the apostrophes
gt;
gt; Consequently, your formula should allow for spaces in a sheet name.
gt; Try this:
gt;
gt; For a sheet name in A1
gt;
gt; A1: Sheet 2
gt; B1: =INDIRECT(quot;'quot;amp;A1amp;quot;'!G5quot;)
gt;
gt; That formula returns the value in cell G5 on the sheet named: quot;Sheet 2quot;
gt;
gt; Is that something you can work with?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;Pesterquot; wrote:
gt;
gt; gt; I understand how to use the INDIRECT Formula but I am getting a #REF error if
gt; gt; the sheet I refer to has a space in between. If My reference is to look for
gt; gt; information from Sheet1 it works, but if it is from Sheet 1 it does not. Any
gt; gt; tips on this? This is critical because all the sheets refer to products and
gt; gt; have spaces in between the name.
gt; gt;
gt; gt; thank you.

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

Pedro

quot;Ron Coderrequot; wrote:

gt; If a sheet name has a space in it, the reference becomes something like this:
gt;
gt; ='Sheet 2'!A1 lt;-note the apostrophes
gt;
gt; Consequently, your formula should allow for spaces in a sheet name.
gt; Try this:
gt;
gt; For a sheet name in A1
gt;
gt; A1: Sheet 2
gt; B1: =INDIRECT(quot;'quot;amp;A1amp;quot;'!G5quot;)
gt;
gt; That formula returns the value in cell G5 on the sheet named: quot;Sheet 2quot;
gt;
gt; Is that something you can work with?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;Pesterquot; wrote:
gt;
gt; gt; I understand how to use the INDIRECT Formula but I am getting a #REF error if
gt; gt; the sheet I refer to has a space in between. If My reference is to look for
gt; gt; information from Sheet1 it works, but if it is from Sheet 1 it does not. Any
gt; gt; tips on this? This is critical because all the sheets refer to products and
gt; gt; have spaces in between the name.
gt; gt;
gt; gt; thank you.