Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Keeping Links Consistent Between Workbooks
I have a series of about 10 workbooks that have active links with each other.
There is one main workbook that all the others link to, and some of the others link to each other also. I've been having some problems with keeping these links consistent when I reformat (insert rows, cut and paste, etc.) the main workbook. I've heard that as long as you have all the workbooks open when you change the main one that the changes should transfer over. Is this true? If not, what else could I do? Also, lets say half the workbooks are in use during the day, and I want to be able to make changes to these workbooks during the day, but implement them at the end of the day. Is this possible? Thanks and I look forward to your feedback |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Keeping Links Consistent Between Workbooks
The workbooks I'm using are pretty extensive and I'd prefer not to have to go
back and name ranges. Are there any other options? Could I use read only versions somehow? Thanks for the Feedback Dave "Dave Peterson" wrote: If you're using formulas with direct links to cells in worksheets in other workbooks like: ='[book1.xls]Sheet''1'!$C$99 Then if you have the "receiving" workbook open when you make the change to the "sending" workbook, then you'll see that the formula adjusts. If it's not possible to have the workbooks open simultaneously, then maybe you could name your range and use that in the formula, like: =book1.xls!myNameHere Then excel will be able to find that cell. (Use Insert|Name|Define to create that name.) wrote: I have a series of about 10 workbooks that have active links with each other. There is one main workbook that all the others link to, and some of the others link to each other also. I've been having some problems with keeping these links consistent when I reformat (insert rows, cut and paste, etc.) the main workbook. I've heard that as long as you have all the workbooks open when you change the main one that the changes should transfer over. Is this true? If not, what else could I do? Also, lets say half the workbooks are in use during the day, and I want to be able to make changes to these workbooks during the day, but implement them at the end of the day. Is this possible? Thanks and I look forward to your feedback -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Keeping Links Consistent Between Workbooks
Maybe...
How often does the sending workbook change? If the people who are editing the sending workbook make changes, won't you be in trouble anyway?? How about an alternative? Create a new worksheet in each of the sending workbooks. Put formulas that point back to the cells on the other sheets for the values you want to retrieve. And protect that worksheet so no changes can be made. Then retrieve the values from that "frozen" worksheet. If a user changes the layout on one sheet, your formula in the other (hidden???) worksheet will updata to point at that new location. But your other workbook won't have to worry about it. And depending on what you're doing, you may be able to use some sort of key column and then you could retrieve the value using =vlookup(). wrote: The workbooks I'm using are pretty extensive and I'd prefer not to have to go back and name ranges. Are there any other options? Could I use read only versions somehow? Thanks for the Feedback Dave "Dave Peterson" wrote: If you're using formulas with direct links to cells in worksheets in other workbooks like: ='[book1.xls]Sheet''1'!$C$99 Then if you have the "receiving" workbook open when you make the change to the "sending" workbook, then you'll see that the formula adjusts. If it's not possible to have the workbooks open simultaneously, then maybe you could name your range and use that in the formula, like: =book1.xls!myNameHere Then excel will be able to find that cell. (Use Insert|Name|Define to create that name.) wrote: I have a series of about 10 workbooks that have active links with each other. There is one main workbook that all the others link to, and some of the others link to each other also. I've been having some problems with keeping these links consistent when I reformat (insert rows, cut and paste, etc.) the main workbook. I've heard that as long as you have all the workbooks open when you change the main one that the changes should transfer over. Is this true? If not, what else could I do? Also, lets say half the workbooks are in use during the day, and I want to be able to make changes to these workbooks during the day, but implement them at the end of the day. Is this possible? Thanks and I look forward to your feedback -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Keeping Links Consistent Between Workbooks
The thins is, the sending workbook will not change during the day. Other
sheets run off it and these need to be used, but the sending workbook will remain the same. Does thsi give you any other ideas? Thanks Dave "Dave Peterson" wrote: Maybe... How often does the sending workbook change? If the people who are editing the sending workbook make changes, won't you be in trouble anyway?? How about an alternative? Create a new worksheet in each of the sending workbooks. Put formulas that point back to the cells on the other sheets for the values you want to retrieve. And protect that worksheet so no changes can be made. Then retrieve the values from that "frozen" worksheet. If a user changes the layout on one sheet, your formula in the other (hidden???) worksheet will updata to point at that new location. But your other workbook won't have to worry about it. And depending on what you're doing, you may be able to use some sort of key column and then you could retrieve the value using =vlookup(). wrote: The workbooks I'm using are pretty extensive and I'd prefer not to have to go back and name ranges. Are there any other options? Could I use read only versions somehow? Thanks for the Feedback Dave "Dave Peterson" wrote: If you're using formulas with direct links to cells in worksheets in other workbooks like: ='[book1.xls]Sheet''1'!$C$99 Then if you have the "receiving" workbook open when you make the change to the "sending" workbook, then you'll see that the formula adjusts. If it's not possible to have the workbooks open simultaneously, then maybe you could name your range and use that in the formula, like: =book1.xls!myNameHere Then excel will be able to find that cell. (Use Insert|Name|Define to create that name.) wrote: I have a series of about 10 workbooks that have active links with each other. There is one main workbook that all the others link to, and some of the others link to each other also. I've been having some problems with keeping these links consistent when I reformat (insert rows, cut and paste, etc.) the main workbook. I've heard that as long as you have all the workbooks open when you change the main one that the changes should transfer over. Is this true? If not, what else could I do? Also, lets say half the workbooks are in use during the day, and I want to be able to make changes to these workbooks during the day, but implement them at the end of the day. Is this possible? Thanks and I look forward to your feedback -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Keeping Links Consistent Between Workbooks
If the workbook doesn't change, then why don't the formulas work?
If the only time the workbook changes is at night (when you do the work), then open all the workbooks and do your updates then. wrote: The thins is, the sending workbook will not change during the day. Other sheets run off it and these need to be used, but the sending workbook will remain the same. Does thsi give you any other ideas? Thanks Dave "Dave Peterson" wrote: Maybe... How often does the sending workbook change? If the people who are editing the sending workbook make changes, won't you be in trouble anyway?? How about an alternative? Create a new worksheet in each of the sending workbooks. Put formulas that point back to the cells on the other sheets for the values you want to retrieve. And protect that worksheet so no changes can be made. Then retrieve the values from that "frozen" worksheet. If a user changes the layout on one sheet, your formula in the other (hidden???) worksheet will updata to point at that new location. But your other workbook won't have to worry about it. And depending on what you're doing, you may be able to use some sort of key column and then you could retrieve the value using =vlookup(). wrote: The workbooks I'm using are pretty extensive and I'd prefer not to have to go back and name ranges. Are there any other options? Could I use read only versions somehow? Thanks for the Feedback Dave "Dave Peterson" wrote: If you're using formulas with direct links to cells in worksheets in other workbooks like: ='[book1.xls]Sheet''1'!$C$99 Then if you have the "receiving" workbook open when you make the change to the "sending" workbook, then you'll see that the formula adjusts. If it's not possible to have the workbooks open simultaneously, then maybe you could name your range and use that in the formula, like: =book1.xls!myNameHere Then excel will be able to find that cell. (Use Insert|Name|Define to create that name.) wrote: I have a series of about 10 workbooks that have active links with each other. There is one main workbook that all the others link to, and some of the others link to each other also. I've been having some problems with keeping these links consistent when I reformat (insert rows, cut and paste, etc.) the main workbook. I've heard that as long as you have all the workbooks open when you change the main one that the changes should transfer over. Is this true? If not, what else could I do? Also, lets say half the workbooks are in use during the day, and I want to be able to make changes to these workbooks during the day, but implement them at the end of the day. Is this possible? Thanks and I look forward to your feedback -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Keeping Links Consistent Between Workbooks
The changes have to be made during the day because as soon as the day is over
the updated workbook needs to be put into use. "Dave Peterson" wrote: If the workbook doesn't change, then why don't the formulas work? If the only time the workbook changes is at night (when you do the work), then open all the workbooks and do your updates then. wrote: The thins is, the sending workbook will not change during the day. Other sheets run off it and these need to be used, but the sending workbook will remain the same. Does thsi give you any other ideas? Thanks Dave "Dave Peterson" wrote: Maybe... How often does the sending workbook change? If the people who are editing the sending workbook make changes, won't you be in trouble anyway?? How about an alternative? Create a new worksheet in each of the sending workbooks. Put formulas that point back to the cells on the other sheets for the values you want to retrieve. And protect that worksheet so no changes can be made. Then retrieve the values from that "frozen" worksheet. If a user changes the layout on one sheet, your formula in the other (hidden???) worksheet will updata to point at that new location. But your other workbook won't have to worry about it. And depending on what you're doing, you may be able to use some sort of key column and then you could retrieve the value using =vlookup(). wrote: The workbooks I'm using are pretty extensive and I'd prefer not to have to go back and name ranges. Are there any other options? Could I use read only versions somehow? Thanks for the Feedback Dave "Dave Peterson" wrote: If you're using formulas with direct links to cells in worksheets in other workbooks like: ='[book1.xls]Sheet''1'!$C$99 Then if you have the "receiving" workbook open when you make the change to the "sending" workbook, then you'll see that the formula adjusts. If it's not possible to have the workbooks open simultaneously, then maybe you could name your range and use that in the formula, like: =book1.xls!myNameHere Then excel will be able to find that cell. (Use Insert|Name|Define to create that name.) wrote: I have a series of about 10 workbooks that have active links with each other. There is one main workbook that all the others link to, and some of the others link to each other also. I've been having some problems with keeping these links consistent when I reformat (insert rows, cut and paste, etc.) the main workbook. I've heard that as long as you have all the workbooks open when you change the main one that the changes should transfer over. Is this true? If not, what else could I do? Also, lets say half the workbooks are in use during the day, and I want to be able to make changes to these workbooks during the day, but implement them at the end of the day. Is this possible? Thanks and I look forward to your feedback -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Keeping Links Consistent Between Workbooks
I don't have any other suggestions.
wrote: The changes have to be made during the day because as soon as the day is over the updated workbook needs to be put into use. "Dave Peterson" wrote: If the workbook doesn't change, then why don't the formulas work? If the only time the workbook changes is at night (when you do the work), then open all the workbooks and do your updates then. wrote: The thins is, the sending workbook will not change during the day. Other sheets run off it and these need to be used, but the sending workbook will remain the same. Does thsi give you any other ideas? Thanks Dave "Dave Peterson" wrote: Maybe... How often does the sending workbook change? If the people who are editing the sending workbook make changes, won't you be in trouble anyway?? How about an alternative? Create a new worksheet in each of the sending workbooks. Put formulas that point back to the cells on the other sheets for the values you want to retrieve. And protect that worksheet so no changes can be made. Then retrieve the values from that "frozen" worksheet. If a user changes the layout on one sheet, your formula in the other (hidden???) worksheet will updata to point at that new location. But your other workbook won't have to worry about it. And depending on what you're doing, you may be able to use some sort of key column and then you could retrieve the value using =vlookup(). wrote: The workbooks I'm using are pretty extensive and I'd prefer not to have to go back and name ranges. Are there any other options? Could I use read only versions somehow? Thanks for the Feedback Dave "Dave Peterson" wrote: If you're using formulas with direct links to cells in worksheets in other workbooks like: ='[book1.xls]Sheet''1'!$C$99 Then if you have the "receiving" workbook open when you make the change to the "sending" workbook, then you'll see that the formula adjusts. If it's not possible to have the workbooks open simultaneously, then maybe you could name your range and use that in the formula, like: =book1.xls!myNameHere Then excel will be able to find that cell. (Use Insert|Name|Define to create that name.) wrote: I have a series of about 10 workbooks that have active links with each other. There is one main workbook that all the others link to, and some of the others link to each other also. I've been having some problems with keeping these links consistent when I reformat (insert rows, cut and paste, etc.) the main workbook. I've heard that as long as you have all the workbooks open when you change the main one that the changes should transfer over. Is this true? If not, what else could I do? Also, lets say half the workbooks are in use during the day, and I want to be able to make changes to these workbooks during the day, but implement them at the end of the day. Is this possible? Thanks and I look forward to your feedback -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Links between workbooks | Excel Worksheet Functions | |||
Keeping data once referenced workbooks are closed. | Excel Discussion (Misc queries) | |||
Keeping Links from Breaking | Excel Discussion (Misc queries) | |||
Links to other workbooks | Excel Worksheet Functions | |||
Keeping 2 Excel workbooks open at the same time | Excel Discussion (Misc queries) |