Keeping Links Consistent Between Workbooks

[adambush4242]

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]

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

 

[adambush4242]

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

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

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]

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

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

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

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

 

[adambush4242]

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

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

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

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



(e-mail address removed) 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]

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.

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

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

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

:

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



(e-mail address removed) 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

 

[adambush4242]

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.

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.

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

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

(e-mail address removed) 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

:

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



(e-mail address removed) 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]

I don't have any other suggestions.

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.

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.

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

:

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

(e-mail address removed) 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

:

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



(e-mail address removed) 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