Why doesn't formula work if sheet is renamed?
 

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)

Why doesn't formula work if sheet is renamed?
 

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)

Why doesn't formula work if sheet is renamed?
 

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)

Why doesn't formula work if sheet is renamed?
 

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
--
David Biddulph

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

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

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

Why doesn't formula work if sheet is renamed?
 

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

Why doesn't formula work if sheet is renamed?
 

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

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

Why doesn't formula work if sheet is renamed?
 

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.
--
Gary''s Student
gsnu200705


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

Why doesn't formula work if sheet is renamed?
 

Maybe...

='John Smith'!A20



Mike wrote:

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

"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

Why doesn't formula work if sheet is renamed?
 

Thanks, Mike and David.
The correct syntax is =John Smith!A20 (Mike)
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)

"Mike" wrote:

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

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

Why doesn't formula work if sheet is renamed?
 

Sorry about the delay in saying 'Thank you", Dave!

Gatsby (Not the Great)

"Dave Peterson" wrote:

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