INDIRECT function error

Hi

I have a strange problem that I can't work out.

In sheet 1 A1 - A10, I have a list of Surnames.
Sheet 2 to sheet 11 are named according to this list.

(all these sheets have been copied from a Template sheet)

If I use =INDIRECT(a1&"!D20") it doesn't pull the value from the relevant
sheet.

One thing I noticed is that if I manually insert a sheet and reference the
name in the INDIRECT function, the formula works. It doesn't seem to work for
the sheets that have been copied

Any ideas?

Hi Anthony,

> If I use =INDIRECT(a1&"!D20") it doesn't pull the value from the relevant
> sheet.

If you have spaces in your sheet names, you'll need single quotes around the
name, so something like the following should work:

=INDIRECT("'"&A1&"'!D20")

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Thanks Stephen, that worked a treat

Infact, I do have spaces in my Sheet Names. I really thought that the
INDIRECT function would 'lookup' exactly, spaces included. Obviously not!

Thanks for your help

"Stephen Bullen" wrote:

> Hi Anthony,
>
> > If I use =INDIRECT(a1&"!D20") it doesn't pull the value from the relevant
> > sheet.
>
> If you have spaces in your sheet names, you'll need single quotes around the
> name, so something like the following should work:
>
> =INDIRECT("'"&A1&"'!D20")
>
> Regards
>
> Stephen Bullen
> Microsoft MVP - Excel
> www.oaltd.co.uk
>
>
>

No, it won't, because the space is the intersection operator.

If you have a column named July and a row named Sales, and you write July
Sales, it means the intersection of the July and Sales ranges.

On Mon, 21 Feb 2005 03:03:02 -0800, "Anthony Slater"
<[email protected]> wrote:

>Thanks Stephen, that worked a treat
>
>Infact, I do have spaces in my Sheet Names. I really thought that the
>INDIRECT function would 'lookup' exactly, spaces included. Obviously not!
>
>Thanks for your help
>
>"Stephen Bullen" wrote:
>
>> Hi Anthony,
>>
>> > If I use =INDIRECT(a1&"!D20") it doesn't pull the value from the relevant
>> > sheet.
>>
>> If you have spaces in your sheet names, you'll need single quotes around
the
>> name, so something like the following should work:
>>
>> =INDIRECT("'"&A1&"'!D20")
>>
>> Regards
>>
>> Stephen Bullen
>> Microsoft MVP - Excel
>> www.oaltd.co.uk
>>
>>
>>