Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel-links in worksheet shows #Ref
I am having a worksheet containing 13 worksheets each representing a calender
month and the last one is consolidation of all worksheets. The text all the worksheets are similar in nature. I have opened 12 columns in the consolidation worksheet and linked all the cells from all worksheets against each month. My problem is whenever I insert a new sheet and rename it for a month) and delete the existing named sheet (eg December), the linked worksheet shoe #REF instead of formulas and I have to manually change the links. Even though the name of the worksheet is the same why excel does not recognize the links after deleting the existing worksheet and inserting a new worksheet named the same? Is there any shortcut method to automatically recognize the new sheet. I think there may be some solution to this problem? Any community member can thro some light on this issue? |
#2
|
|||
|
|||
Excel-links in worksheet shows #Ref
The links are not static. When you change the name of a worksheet, any links
in the same workbook will update with the new name. Equally, when you delete the "December" worksheet, the links to it can no longer be resolved, so they are replaced with the #REF. The new "December" worksheet has the same name, but it is NOT the same worksheet. There are a few possible ways round this. If you just don't want to have to retype the formulae by hand, you can simply use FindReplace and replace "#REF" with "December." You could try renaming the old sheet to "Dec_Old", say, then create the new sheet and us FindReplace "Dec_Old" with "December" on your summary page. Depending on what kind of consolidation you're doing, a better way might be to change the formulae entirely. If you're just doing a simple total of the same cell in all the worksheets, you can replace =Jan!A2+Feb!A2...+Dec!A2 with =SUM('Jan:Dec'!A2) That way, it won't matter if you delete any of the intervening worksheets, as long as your replacement goes back in the sequence. Of course, this will still cause problems if you want to delete either "Jan" or "Dec." The way round this is to have a hidden worksheet at either end of the ones you want to calculate. For Example: New Worksheet layout: Start..Jan..Feb..Dec..End New Summary formula: =SUM('Start:End'!A2) Cheers, Pete "srinivasan" wrote: I am having a worksheet containing 13 worksheets each representing a calender month and the last one is consolidation of all worksheets. The text all the worksheets are similar in nature. I have opened 12 columns in the consolidation worksheet and linked all the cells from all worksheets against each month. My problem is whenever I insert a new sheet and rename it for a month) and delete the existing named sheet (eg December), the linked worksheet shoe #REF instead of formulas and I have to manually change the links. Even though the name of the worksheet is the same why excel does not recognize the links after deleting the existing worksheet and inserting a new worksheet named the same? Is there any shortcut method to automatically recognize the new sheet. I think there may be some solution to this problem? Any community member can thro some light on this issue? |
#3
|
|||
|
|||
Excel-links in worksheet shows #Ref
You could also try the INDIRECT function
=INDIRECT("Sheet2!A1") However, the link is not dynamic. If you go to Sheet2 and insert a row (moving the data in cell A1 to A2) your reference will not update to reflect the new location of your data. "srinivasan" wrote: I am having a worksheet containing 13 worksheets each representing a calender month and the last one is consolidation of all worksheets. The text all the worksheets are similar in nature. I have opened 12 columns in the consolidation worksheet and linked all the cells from all worksheets against each month. My problem is whenever I insert a new sheet and rename it for a month) and delete the existing named sheet (eg December), the linked worksheet shoe #REF instead of formulas and I have to manually change the links. Even though the name of the worksheet is the same why excel does not recognize the links after deleting the existing worksheet and inserting a new worksheet named the same? Is there any shortcut method to automatically recognize the new sheet. I think there may be some solution to this problem? Any community member can thro some light on this issue? |
#4
|
|||
|
|||
Excel-links in worksheet shows #Ref
Thank you Mr Pete McCosh for the enlightment on the subject.
srinivasan.v |
#5
|
|||
|
|||
Excel-links in worksheet shows #Ref
Thank you for the response Mr JMB
srinivasan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
importing data from Excel worksheet to another worksheet | Excel Worksheet Functions | |||
Excel worksheet display #### | Excel Discussion (Misc queries) | |||
exporting excel worksheet to word | Links and Linking in Excel | |||
How can I protect an excel worksheet containing filters? | Excel Discussion (Misc queries) | |||
Copying blank excel worksheet into additional excel workbooks | Excel Discussion (Misc queries) |