Syntax on Indirect()
 

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

Syntax on Indirect()
 

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

"Jim May" wrote:

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

Syntax on Indirect()
 

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)



"Jim May" wrote in message
...
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

Syntax on Indirect()
 

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

"Jim May" wrote in message
...
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

Syntax on Indirect()
 

Thanks to you Joel, Chip and Bernard

"Bernard Liengme" wrote:

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

"Jim May" wrote in message
...
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