| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula changes to renamed cell name. I want to keep it from chang | Excel Discussion (Misc queries) | |||
| How do I copy a formula from one work sheet to another? | Excel Worksheet Functions | |||
| Renamed invalid sheet name | Excel Discussion (Misc queries) | |||
| having renamed a workbook my password does not work | Excel Discussion (Misc queries) | |||
| Need help to set-up a formula on Excel work sheet? | New Users to Excel | |||
Linear Mode
