Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
broken links
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?? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
broken links
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
broken links
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
broken links
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
broken links
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
broken links
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Broken Links.... | Excel Discussion (Misc queries) | |||
Broken Links | Excel Discussion (Misc queries) | |||
broken links message still popping up | Excel Discussion (Misc queries) | |||
Links are broken and cannot fix | Excel Worksheet Functions | |||
upgrade to excel 2003 update broken links error | Links and Linking in Excel |