Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
srinivasan
 
Posts: n/a
Default 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   Report Post  
Pete McCosh
 
Posts: n/a
Default 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   Report Post  
JMB
 
Posts: n/a
Default 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   Report Post  
srinivasan
 
Posts: n/a
Default Excel-links in worksheet shows #Ref

Thank you Mr Pete McCosh for the enlightment on the subject.
srinivasan.v
  #5   Report Post  
srinivasan
 
Posts: n/a
Default Excel-links in worksheet shows #Ref

Thank you for the response Mr JMB
srinivasan
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
importing data from Excel worksheet to another worksheet jbrick Excel Worksheet Functions 0 August 11th 05 05:51 PM
Excel worksheet display #### pang_kimyam Excel Discussion (Misc queries) 3 August 10th 05 01:06 PM
exporting excel worksheet to word morrowkd Links and Linking in Excel 1 May 11th 05 11:23 PM
How can I protect an excel worksheet containing filters? JMcG Excel Discussion (Misc queries) 4 April 13th 05 03:13 PM
Copying blank excel worksheet into additional excel workbooks Deb Excel Discussion (Misc queries) 10 February 4th 05 01:25 AM


All times are GMT +1. The time now is 06:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"