INDIRECT function error

A

Anthony Slater

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?
 
S

Stephen Bullen

Hi Anthony,
If I use =INDIRECT(a1&"!D20") it doesn't pull the value from the relevant
sheet.
Click to expand...

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
 
A

Anthony Slater

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
 
M

Myrna Larson

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.
 
Top