Excel-links in worksheet shows #Ref

[Guest]

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?

 

[Guest]

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 Find>>Replace and replace
"#REF" with "December." You could try renaming the old sheet to "Dec_Old",
say, then create the new sheet and us Find>>Replace "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('Janec'!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

 

[Guest]

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.

 

[Guest]

Thank you Mr Pete McCosh for the enlightment on the subject.
srinivasan.v

 

[Guest]

Thank you for the response Mr JMB
srinivasan