Linking workbooks
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. |
Linking workbooks
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" wrote: 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. |
Linking workbooks
On Mar 23, 2:50*pm, OssieMac
wrote: 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" wrote: 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. |
Linking workbooks
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 |
Linking workbooks
On Mar 24, 4:21*pm, OssieMac
wrote: 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. |
All times are GMT +1. The time now is 10:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com