Syntax on Indirect()

[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

 

[Joel]

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

 

[Chip Pearson]

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)

 

[Bernard Liengme]

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

 

[Jim May]

Thanks to you Joel, Chip and Bernard

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