Syntax on Indirect()

  • Thread starter Thread starter Jim May
  • Start date Start date
J

Jim May

I have a Sheet named Baylor
and in Cell B13 of that sheet I have 321

Also on Sheet1 in Cell A1 I have Baylor
In cell B1 (of Sheet1) I have B13

On my Sheet1 - Cell C3 I currently have =INDIRECT("' & $A$1&'"&"!"&B1)
But it is returning #REF! instead of 321
Can someone point out my syntax error above?
TIA,

Jim
 
Watch your quotes. The following works.

=INDIRECT("'"&$A$1&"'!"&B1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Joel has given a good answer with =INDIRECT($A$1&"!"&B1)

But if the worksheet's name has spaces in it then some single quotes are
needed
=INDIRECT("'"&$A$1&"'!"&B1)
Before the first & we have double quote, single quote then double quote
After the second quote we have double quote then single quote

best wishes
 
Thanks to you Joel, Chip and Bernard

Bernard Liengme said:
Joel has given a good answer with =INDIRECT($A$1&"!"&B1)

But if the worksheet's name has spaces in it then some single quotes are
needed
=INDIRECT("'"&$A$1&"'!"&B1)
Before the first & we have double quote, single quote then double quote
After the second quote we have double quote then single quote

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email
Click to expand...
 
Back
Top