Why doesn't formula work if sheet is renamed?

G

Gatsby

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)
 
M

Mike

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
 
G

Gatsby

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)
 
D

David Biddulph

Please read again what Mike said in his post. Type the = in the formula
cell, then click in your johnsmith A20 cell, & the formula should include
the link correctly.

Presumably ='John Smith'!D13
 
D

Dave Peterson

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.
 
M

Mike

The spaces that catch people out most in sheet tabs names are either leading
or trailing spaces and not spaces between words. Did you try navigating to
the cell you are trying to copy instead of simply entering the formula?

The correct syntax is =John Smith!A20

Mike
 
G

Gary''s Student

Try making the formula with only the mouse:

1. in a cell touch =
2. with mouse click on the johnsmith tab
3. in the johnsmith tab click on A20 with the mouse
4. touch ENTER

Examine the resulting formula in the original tab.
 
D

Dave Peterson

Maybe...

='John Smith'!A20


The spaces that catch people out most in sheet tabs names are either leading
or trailing spaces and not spaces between words. Did you try navigating to
the cell you are trying to copy instead of simply entering the formula?

The correct syntax is =John Smith!A20

Mike
Click to expand...
 
G

Gatsby

Thanks, Mike and David.
The correct syntax is =John Smith!A20 (Mike)
Click to expand...
No. Didn't work!

Presumably ='John Smith'!D13 (David)
Yes. That works!
Must be the ' ' around the name that I was missing.
What would I do without the kindness of you guys?
I'D BE LOST!!

Thanks,
Gatsby (Not the Great)
 
Top