Linking workbooks

[HillClimbinGirl]

I have come to this excel group on many occasions, and have had most
of my questions answered by searching through the posts. So I thank
all who contribute.

This problem, though, eludes me and I can't seem to find it in the
groups. I use Excel 2003. My problem is this...

I have two Workbooks - "Revenue Worksheet" and "2009 Revenue".

From Revenue Worksheet I want to link cells B162:S162 (which is a
total of a certain amount of columns) to a cell range in 2009
Revenue. Which I can do.

The problem is that in Revenue Worksheet rows maybe added (1, 2, 3
rows etc at varying times) before the total row a month or 6 down the
line. Once those rows are added 2009 Revenue still links to 162.
When infact it is now 170. I have to continually go back to 2009
Revenue to change the linked cells. (And find that I have to do each
cell at a time.)

My question is, is there anyway to make 2009 Revenue pull from that
Total row in Revenue worksheet as it moves to different cells?

It seems that this works when it is within the same workbook, but I
cannot get it to work in different workbooks.

I hope this all makes sense, as sometimes what makes sense to me,
doesn't make sense to most other people. If you need clarification
please let me know.

Thanking all in advance for any thoughts on this.

 

[OssieMac]

Try definining a name for the source range and edit the link to reflect that
name.

Example: Define name for the source range MyLinkedRange (See help for how)

Then edit the following formula for the link.
='[2009 Revenue.xlsm]Sheet1'!$B$4:$S$4

to the following:-

='2009 Revenue.xlsm'!MyLinkedRange

I was not certain that I interpreted correctly which workbook is the source
and which is the one containing the link but the above should give you the
idea.

Regards,

OssieMac

 

[HillClimbinGirl]

Try definining a name for the source range and edit the link to reflect that
name.

Example: Define name for the source range MyLinkedRange  (See help for how)

Then edit the following formula for the link.
='[2009 Revenue.xlsm]Sheet1'!$B$4:$S$4

to the following:-

='2009 Revenue.xlsm'!MyLinkedRange

I was not certain that I interpreted correctly which workbook is the source
and which is the one containing the link but the above should give you the
idea.

Regards,

OssieMac

I have come to this excel group on many occasions, and have had most
of my questions answered by searching through the posts.  So I thank
all who contribute.

This problem, though, eludes me and I can't seem to find it in the
groups. I use Excel 2003.  My problem is this...

I have two Workbooks - "Revenue Worksheet" and "2009 Revenue".

From Revenue Worksheet I want to link cells B162:S162 (which is a
total of a certain amount of columns) to a cell range in 2009
Revenue.  Which I can do.

The problem is that in Revenue Worksheet rows maybe added (1, 2, 3
rows etc at varying times) before the total row a month or 6 down the
line.  Once those rows are added 2009 Revenue still links to 162.
When infact it is now 170.  I have to continually go back to 2009
Revenue to change the linked cells. (And find that I have to do each
cell at a time.)

My question is, is there anyway to make 2009 Revenue pull from that
Total row in Revenue worksheet as it moves to different cells?

It seems that this works when it is within the same workbook, but I
cannot get it to work in different workbooks.

I hope this all makes sense, as sometimes what makes sense to me,
doesn't make sense to most other people.   If you need clarification
please let me know.

Thanking all in advance for any thoughts on this.- Hide quoted text -

- Show quoted text -

Thank you for the response. While this sounds like it may work, I
cannot figure out how to do it. I believe the that I have figured out
how to Define Name. But I cannot seem to figure out how to reference
that in the destination (2009 Revenue) workbook.

Also in addition to my original post, if both workbooks are open, the
destination workbook link will work. The problem is when once saved
and closed, any rows added to the source (Revenue Worksheet) without
the destination workbook being open, it will not work and keep the
previous cells that the formula indicates.

 

[OssieMac]

I understand the following:-
Source workbook (where the original data is) is Revenue Worksheet.
Destination workbook (where the link is created) is 2009 Revenue.
If I have the above reversed then you will have to reverse the workbooks in
the following instructions.

Open both workbooks.
In the Source workbook (Revenue Worksheet)
Select the range of cells to be linked (B162:S162)
Click menu item Insert -> Name -> Define
In field under Names in workbook type a name like MySourceRange (No spaces).
Click OK
Save the workbook
KEEP THE WORKBOOK OPEN.

In Destination workbook (2009 Revenue)
Select the FIRST cell of the range where you want the links to appear. (Say
B20)
Click in the formula bar.
Type the = sign to start formula (DO NOT press Enter)
Select the Source workbook (Revenue Worksheet) (either VIA the task bar or
via menu item Windows.)
Click menu item Edit -> Go To
In the dialog box select the name you created (MySourceRange) and click OK.
Press Enter and you will return to the Destination workbook window.
Copy the link to the full required range across the worksheet.
Save the workbook.

Now save and close the source workbook. In the Destination workbook you will
see the formula has updated itself to include the full path of the source.

Save and close the Destination workbook.

You can now open either workbook individually without the other. However, if
you open the Destination workbook without the Source open then you will get a
dialog box asking you to update the links. Select Update.

You can add or delete rows above either the data in the source or above the
data in the destination data and the linked data will be unaffected.

Regards,

OssieMac

 

[HillClimbinGirl]

I understand the following:-
Source workbook (where the original data is) is Revenue Worksheet.
Destination workbook (where the link is created) is 2009 Revenue.
If I have the above reversed then you will have to reverse the workbooks in
the following instructions.

Open both workbooks.
In the Source workbook (Revenue Worksheet)
Select the range of cells to be linked (B162:S162)
Click menu item Insert -> Name -> Define
In field under Names in workbook type a name like MySourceRange (No spaces).
Click OK
Save the workbook
KEEP THE WORKBOOK OPEN.

In Destination workbook (2009 Revenue)
Select the FIRST cell of the range where you want the links to appear. (Say
B20)
Click in the formula bar.
Type the = sign to start formula (DO NOT press Enter)
Select the Source workbook (Revenue Worksheet) (either VIA the task bar or
via menu item Windows.)
Click menu item Edit -> Go To
In the dialog box select the name you created (MySourceRange) and click OK.
Press Enter and you will return to the Destination workbook window.
Copy the link to the full required range across the worksheet.
Save the workbook.

Now save and close the source workbook. In the Destination workbook you will
see the formula has updated itself to include the full path of the source..

Save and close the Destination workbook.

You can now open either workbook individually without the other. However,if
you open the Destination workbook without the Source open then you will get a
dialog box asking you to update the links. Select Update.

You can add or delete rows above either the data in the source or above the
data in the destination data and the linked data will be unaffected.

Regards,

OssieMac

Thank you so very much for taking the time to type out the
instructions! This worked perfectly. I was also pleasantly suprised
that the one Define Name worked for each cell. I thought I was going
to have to make a name for each individual cell in order for it to
work in the destination Cells. But it automatically picked up the
right cell.
I can't thank you enough for all the future time you have saved me.