broken links

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+...)

"Doug T" wrote:

> 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??

Maybe you could post the formula that breaks when you move the worksheet.

Post the before and after versions.

Doug T wrote:
>
> 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??

--

Dave Peterson

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.

"Dave Peterson" wrote:

> Maybe you could post the formula that breaks when you move the worksheet.
>
> Post the before and after versions.
>
> Doug T wrote:
> >
> > 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??
>
> --
>
> Dave Peterson
>

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.


Doug T wrote:
>
> 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.
>
> "Dave Peterson" wrote:
>
> > Maybe you could post the formula that breaks when you move the worksheet.
> >
> > Post the before and after versions.
> >
> > Doug T wrote:
> > >
> > > 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??
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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.


"Dave Peterson" wrote:

> 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.
>
>
> Doug T wrote:
> >
> > 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.
> >
> > "Dave Peterson" wrote:
> >
> > > Maybe you could post the formula that breaks when you move the worksheet.
> > >
> > > Post the before and after versions.
> > >
> > > Doug T wrote:
> > > >
> > > > 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??
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Please post back with your results. I'm interested in what you find out.

Doug T wrote:
>
> 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.
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> >
> > Doug T wrote:
> > >
> > > 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.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Maybe you could post the formula that breaks when you move the worksheet.
> > > >
> > > > Post the before and after versions.
> > > >
> > > > Doug T wrote:
> > > > >
> > > > > 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??
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson