broken links

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'll do my best to make this short.
I have workbook with a number of worksheets that interact with each other
through links. The idea is that if I change the information contained in a
cell on one sheet, the linked info on another sheet will also change. I set
this up using Paste Special/Paste Link.
So, to explain the problem, say I have 4 sheets of data. I create a 5th
sheet by copying a template I have set up, and then Paste Special/Paste Link
data from any or all of the original 4 to the new 5th sheet. Everything works
fine on the 5th sheet as long as I don't move it. Changes I make in the
linked sheets are reflected on the 5th sheet as intended. Then, when I drag
the new sheet (the tab) to another location in the workbook, say between 2
and 3, the links to the new sheet quit working and some of the others quit as
well. I have tried this on 3 different computers and the result is the same.
Any ideas??
 
Guess you could have formulas referring to range of worksheets in your
original sheets (Sheets 1-4 referred in your post). Try removing the range of
worksheet reference in formulas and change it to specific sheets and
cells(For eg: Sum(Sheet1:Sheet4 A1) used for range of sheets instead try
Sheet1A1+Sheet2A2+...)
 
Maybe you could post the formula that breaks when you move the worksheet.

Post the before and after versions.
 
Thanks for the replies. To be more specific, the sheets in the workbook
contain names for a schedule. Each sheet covers a one week period. Each sheet
is linked to the one before it (Paste Special/Paste Link) so that if a name
changes in any particular week, it will be changed in all weeks that follow.
So the formula in each cell is merely an instruction to place the same name
in the same cell reference on all sheets that follow ie ='Zone Nov
20-26'!D26. Any new sheets that are added automatically insert before sheet
#1 so I have to move them to the end to keep them in order. When I move the
new sheet to the end, the links between the other sheets quit working. Giving
Excel a kickstart using the Find/Replace = trick fixes the problem until I
add another sheet. So now I can fix the damage, but I'd like to stop it from
occuring in the first place.
 
Since your formulas refer directly (by name) to a different sheet, then I'm not
sure why the position of the worksheet would have anything to do with how that
formula evaluates.

My first guess is that calculation was set to manual, but that shouldn't break
the existing value in that cell with the formula.

But if you change D26 on 'Zone Nov 20-26' and calculation is set to manual, you
won't see the formula change until excel recalculates that cell with the
formula.

I'd look at tools|options|calculation tab to verify that you're using automatic
calculation.
 
I agree, position should not make a difference yet moving one sheet affects
all sheets. I checked Tools/Options/Calculations and it is set to automatic.
I tried doing a manual calculation while it was misbehaving but that didn't
help. I also compared the before and after version of the formula and they
appear to be the same. I've used this workbook on three different computers
and the problem has reared it's ugly head on all three so it's not a glitch
in any one installation of Excel. It appears to be workbook specific or at
very least a flaw in how Excel handles this type of situation. Regardless, it
is extremely annoying. I think my next step is to rebuild in a new workbook.
 
Please post back with your results. I'm interested in what you find out.

Doug said:
I agree, position should not make a difference yet moving one sheet affects
all sheets. I checked Tools/Options/Calculations and it is set to automatic.
I tried doing a manual calculation while it was misbehaving but that didn't
help. I also compared the before and after version of the formula and they
appear to be the same. I've used this workbook on three different computers
and the problem has reared it's ugly head on all three so it's not a glitch
in any one installation of Excel. It appears to be workbook specific or at
very least a flaw in how Excel handles this type of situation. Regardless, it
is extremely annoying. I think my next step is to rebuild in a new workbook.
Click to expand...
 
Back
Top