If there's a space in that worksheet name, you'll need to surround it with
apostrophes:

='John Smith'!d13

But if you tried Mike's suggestion:

Type the equal sign in the cell with the formula
then off to D13 on the sheet named John Smith
then click on that cell and hit enter

Then excel would have done the heavy lifting and you won't have to worry about
the syntax.

Another way.
Select D13 on John Smith
edit|copy
go to the cell that gets the formula
Edit|Paste special|Paste Link

Another way.
Rename the sheet to A
write your formula
=a!d13
Then rename A back to John Smith
Excel will do the heavy lifting here, too.


Gatsby wrote:

Thanks for replying, Mike. That doesn't work for me.
John Smith has a space tween John and Smith on the tab. I've tried =John
Smith!D13 and Johnsmith!D13. A box appears "Update Values Book".
I notice in your post you leave out the '!'. I've tried that too.
Aaaaaaaaaagh!
Gatsby (Not the Great)

"Mike" wrote:

Its sounds like you may have some rogue spaces in either the sheet name or
the formula.

In the formula cell enter = then navigate to johnsmith A20 and see if that
works.

Mike

"Gatsby" wrote:

If I enter =Sheet2!A20 into a cell in another sheet the value appears. Great!
If I enter =JohnSmith!A20 it doesnt work. Why? It asks for the location of
John Smith even though his sheet is in same workbook.
Thanks

Gatsby (Not the Great)

--

Dave Peterson