Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Anthony Slater
 
Posts: n/a
Default 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?


  #2   Report Post  
Stephen Bullen
 
Posts: n/a
Default

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


  #3   Report Post  
Anthony Slater
 
Posts: n/a
Default

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



  #4   Report Post  
Myrna Larson
 
Posts: n/a
Default

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"
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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
INDIRECT function question Joe Excel Worksheet Functions 1 February 14th 05 04:54 PM
Indirect( ) function loosing values when spreadsheets are closed Word4Dummies Excel Worksheet Functions 2 February 13th 05 01:41 PM
Erf (the error function) for both negative and positive numbers Kara Excel Discussion (Misc queries) 0 February 7th 05 02:11 AM
Match Function - no room for error Dee Veloper Excel Worksheet Functions 0 January 29th 05 12:58 AM
compile error: expected variable or function MMM Excel Discussion (Misc queries) 3 December 24th 04 04:11 PM


All times are GMT +1. The time now is 02:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"