Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells no longer linked after inserts
I linked several worksheets with a mastersheet and the info updates fine,
until I insert a new column. Some of the linked cell formula change to incorporate the new postition on the mastersheet, but others don't. I end up having to change the formula so they can match the new position. Is there anyway I can stop this from happening so that when the originals move position the linked cells continue to reflect the correct info. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells no longer linked after inserts
Are these worksheets in a single workbook?
If yes, then I've never seen this with "normal" formulas. You may want to share the formula that doesn't update when you make these changes. If no, then you could open all the workbooks (in the same instance of excel) that are linked and make the changes. Excel will update the formulas to reflect your changes. Remember to save all the files so that those changes excel made are saved. If you only have a few cells that you're retrieving, you could keep that file with the links (the "receiving" file) closed, but use a named range (insert|Name|Define). Then your formula would use that name instead of an address. SF wrote: I linked several worksheets with a mastersheet and the info updates fine, until I insert a new column. Some of the linked cell formula change to incorporate the new postition on the mastersheet, but others don't. I end up having to change the formula so they can match the new position. Is there anyway I can stop this from happening so that when the originals move position the linked cells continue to reflect the correct info. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells no longer linked after inserts
Everything is in the same workbook. I use the formula =mastersheet!A1 (just
an example) to update all the other list, but there are times where the list will keep the original formula and not change to incorporate the new position of the original. I hate having to keep going back to figure out which cells are no longer reflecting the right information. "Dave Peterson" wrote: Are these worksheets in a single workbook? If yes, then I've never seen this with "normal" formulas. You may want to share the formula that doesn't update when you make these changes. If no, then you could open all the workbooks (in the same instance of excel) that are linked and make the changes. Excel will update the formulas to reflect your changes. Remember to save all the files so that those changes excel made are saved. If you only have a few cells that you're retrieving, you could keep that file with the links (the "receiving" file) closed, but use a named range (insert|Name|Define). Then your formula would use that name instead of an address. SF wrote: I linked several worksheets with a mastersheet and the info updates fine, until I insert a new column. Some of the linked cell formula change to incorporate the new postition on the mastersheet, but others don't. I end up having to change the formula so they can match the new position. Is there anyway I can stop this from happening so that when the originals move position the linked cells continue to reflect the correct info. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells no longer linked after inserts
Inserting/deleting rows/columns won't break these formulas.
They'll adjust to point to whereever that A1 cell got moved to. On the other hand, if you wanted the formula to always refer to A1 no matter what you inserted, you could use: =indirect("mastersheet!a1") ===== But my guess is that you're sorting this mastersheet and excel won't help you here. I'd try to add a unique key that I can match up on and retrieve information based on that key. Debra Dalgleish has lots of notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://www.contextures.com/xlFunctions03.html (for =index(match())) and http://contextures.com/xlFunctions02.html#Trouble SF wrote: Everything is in the same workbook. I use the formula =mastersheet!A1 (just an example) to update all the other list, but there are times where the list will keep the original formula and not change to incorporate the new position of the original. I hate having to keep going back to figure out which cells are no longer reflecting the right information. "Dave Peterson" wrote: Are these worksheets in a single workbook? If yes, then I've never seen this with "normal" formulas. You may want to share the formula that doesn't update when you make these changes. If no, then you could open all the workbooks (in the same instance of excel) that are linked and make the changes. Excel will update the formulas to reflect your changes. Remember to save all the files so that those changes excel made are saved. If you only have a few cells that you're retrieving, you could keep that file with the links (the "receiving" file) closed, but use a named range (insert|Name|Define). Then your formula would use that name instead of an address. SF wrote: I linked several worksheets with a mastersheet and the info updates fine, until I insert a new column. Some of the linked cell formula change to incorporate the new postition on the mastersheet, but others don't. I end up having to change the formula so they can match the new position. Is there anyway I can stop this from happening so that when the originals move position the linked cells continue to reflect the correct info. -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells no longer linked after inserts
So what your saying is that if I insert a column the formula shouldn't
change, but if I try to sort the mastersheet, the formula will not follow suit...? "Dave Peterson" wrote: Inserting/deleting rows/columns won't break these formulas. They'll adjust to point to whereever that A1 cell got moved to. On the other hand, if you wanted the formula to always refer to A1 no matter what you inserted, you could use: =indirect("mastersheet!a1") ===== But my guess is that you're sorting this mastersheet and excel won't help you here. I'd try to add a unique key that I can match up on and retrieve information based on that key. Debra Dalgleish has lots of notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://www.contextures.com/xlFunctions03.html (for =index(match())) and http://contextures.com/xlFunctions02.html#Trouble SF wrote: Everything is in the same workbook. I use the formula =mastersheet!A1 (just an example) to update all the other list, but there are times where the list will keep the original formula and not change to incorporate the new position of the original. I hate having to keep going back to figure out which cells are no longer reflecting the right information. "Dave Peterson" wrote: Are these worksheets in a single workbook? If yes, then I've never seen this with "normal" formulas. You may want to share the formula that doesn't update when you make these changes. If no, then you could open all the workbooks (in the same instance of excel) that are linked and make the changes. Excel will update the formulas to reflect your changes. Remember to save all the files so that those changes excel made are saved. If you only have a few cells that you're retrieving, you could keep that file with the links (the "receiving" file) closed, but use a named range (insert|Name|Define). Then your formula would use that name instead of an address. SF wrote: I linked several worksheets with a mastersheet and the info updates fine, until I insert a new column. Some of the linked cell formula change to incorporate the new postition on the mastersheet, but others don't. I end up having to change the formula so they can match the new position. Is there anyway I can stop this from happening so that when the originals move position the linked cells continue to reflect the correct info. -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells no longer linked after inserts
If you have a simple formula like this in A1 of Sheet1:
=mastersheet!a99 Then if you sort the mastersheet, your formula in A1 of Sheet1 will still look like: =mastersheet!a99 And unless you're very, very lucky, the value in A99 in the mastersheet will be different. And I'm saying that if you have a formula like: =mastersheet!c9 and insert a new column A, then your formula will get adjusted to: =mastersheet!d9 SF wrote: So what your saying is that if I insert a column the formula shouldn't change, but if I try to sort the mastersheet, the formula will not follow suit...? "Dave Peterson" wrote: Inserting/deleting rows/columns won't break these formulas. They'll adjust to point to whereever that A1 cell got moved to. On the other hand, if you wanted the formula to always refer to A1 no matter what you inserted, you could use: =indirect("mastersheet!a1") ===== But my guess is that you're sorting this mastersheet and excel won't help you here. I'd try to add a unique key that I can match up on and retrieve information based on that key. Debra Dalgleish has lots of notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://www.contextures.com/xlFunctions03.html (for =index(match())) and http://contextures.com/xlFunctions02.html#Trouble SF wrote: Everything is in the same workbook. I use the formula =mastersheet!A1 (just an example) to update all the other list, but there are times where the list will keep the original formula and not change to incorporate the new position of the original. I hate having to keep going back to figure out which cells are no longer reflecting the right information. "Dave Peterson" wrote: Are these worksheets in a single workbook? If yes, then I've never seen this with "normal" formulas. You may want to share the formula that doesn't update when you make these changes. If no, then you could open all the workbooks (in the same instance of excel) that are linked and make the changes. Excel will update the formulas to reflect your changes. Remember to save all the files so that those changes excel made are saved. If you only have a few cells that you're retrieving, you could keep that file with the links (the "receiving" file) closed, but use a named range (insert|Name|Define). Then your formula would use that name instead of an address. SF wrote: I linked several worksheets with a mastersheet and the info updates fine, until I insert a new column. Some of the linked cell formula change to incorporate the new postition on the mastersheet, but others don't. I end up having to change the formula so they can match the new position. Is there anyway I can stop this from happening so that when the originals move position the linked cells continue to reflect the correct info. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells no longer linked after inserts
Added a couple of words just to clarify...
And I'm saying that if you have a formula like: =mastersheet!c9 and insert a new column A *in the mastersheet*, then your formula will get adjusted to: =mastersheet!d9 Dave Peterson wrote: If you have a simple formula like this in A1 of Sheet1: =mastersheet!a99 Then if you sort the mastersheet, your formula in A1 of Sheet1 will still look like: =mastersheet!a99 And unless you're very, very lucky, the value in A99 in the mastersheet will be different. And I'm saying that if you have a formula like: =mastersheet!c9 and insert a new column A, then your formula will get adjusted to: =mastersheet!d9 SF wrote: So what your saying is that if I insert a column the formula shouldn't change, but if I try to sort the mastersheet, the formula will not follow suit...? "Dave Peterson" wrote: Inserting/deleting rows/columns won't break these formulas. They'll adjust to point to whereever that A1 cell got moved to. On the other hand, if you wanted the formula to always refer to A1 no matter what you inserted, you could use: =indirect("mastersheet!a1") ===== But my guess is that you're sorting this mastersheet and excel won't help you here. I'd try to add a unique key that I can match up on and retrieve information based on that key. Debra Dalgleish has lots of notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://www.contextures.com/xlFunctions03.html (for =index(match())) and http://contextures.com/xlFunctions02.html#Trouble SF wrote: Everything is in the same workbook. I use the formula =mastersheet!A1 (just an example) to update all the other list, but there are times where the list will keep the original formula and not change to incorporate the new position of the original. I hate having to keep going back to figure out which cells are no longer reflecting the right information. "Dave Peterson" wrote: Are these worksheets in a single workbook? If yes, then I've never seen this with "normal" formulas. You may want to share the formula that doesn't update when you make these changes. If no, then you could open all the workbooks (in the same instance of excel) that are linked and make the changes. Excel will update the formulas to reflect your changes. Remember to save all the files so that those changes excel made are saved. If you only have a few cells that you're retrieving, you could keep that file with the links (the "receiving" file) closed, but use a named range (insert|Name|Define). Then your formula would use that name instead of an address. SF wrote: I linked several worksheets with a mastersheet and the info updates fine, until I insert a new column. Some of the linked cell formula change to incorporate the new postition on the mastersheet, but others don't. I end up having to change the formula so they can match the new position. Is there anyway I can stop this from happening so that when the originals move position the linked cells continue to reflect the correct info. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create IF function that inserts cells when condition is m | Excel Worksheet Functions | |||
Sorting non-linked cells in a linked worksheet | Excel Discussion (Misc queries) | |||
Linked Cells Staying With Cells Once Linked Workbook Update. | Excel Worksheet Functions | |||
No longer able to copy cells | Excel Discussion (Misc queries) | |||
Cells no longer highlight when selected | Excel Discussion (Misc queries) |