Linking workbooks #5

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.
0
mihart.m (3)
3/23/2009 5:52:21 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
285 Views

Similar Articles

[PageSpeed] 33

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.
> 
0
OssieMac (238)
3/23/2009 8:50:01 PM
On Mar 23, 2:50=A0pm, OssieMac <Ossie...@discussions.microsoft.com>
wrote:
> Try definining a name for the source range and edit the link to reflect t=
hat
> name.
>
> Example: Define name for the source range MyLinkedRange =A0(See help for =
how)
>
> Then edit the following formula for the link.
> =3D'[2009 Revenue.xlsm]Sheet1'!$B$4:$S$4
>
> to the following:-
>
> =3D'2009 Revenue.xlsm'!MyLinkedRange
>
> I was not certain that I interpreted correctly which workbook is the sour=
ce
> and which is the one containing the link but the above should give you th=
e
> 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. =A0So 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. =A0My 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. =A0Which 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. =A0Once those rows are added 2009 Revenue still links to 162.
> > When infact it is now 170. =A0I 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. =A0 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.

0
mihart.m (3)
3/24/2009 7:42:49 PM
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


0
OssieMac (238)
3/24/2009 10:21:03 PM
On Mar 24, 4:21=A0pm, OssieMac <Ossie...@discussions.microsoft.com>
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 space=
s).
> 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. (S=
ay
> B20)
> Click in the formula bar.
> Type the =3D sign to start formula (DO NOT press Enter)
> Select the Source workbook (Revenue Worksheet) (either VIA the task bar o=
r
> via menu item Windows.)
> Click menu item Edit -> Go To
> In the dialog box select the name you created (MySourceRange) and click O=
K.
> 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 w=
ill
> 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 g=
et 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 t=
he
> 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.
0
mihart.m (3)
3/25/2009 4:51:59 PM
Reply:

Similar Artilces:

exchange version 6.5 build 7226.6: service pack 1 delivers blank m
hi, we use exchange 2003 sp1(version 6.5 build 7226.6: service pack 1) and we have the following problem: - when some of our PDA users sends an e-mail - some of the exchange recipients that receive the e-mail as part of a group - can see no body for the e-mail - it is just blank; on the client side users user outlook 2003. Those are the headers of the two e-mails: Header of the e-mail taken from recipients mailbox - there is no e-mail body - empty/blank: ==================== Microsoft Mail Internet Headers Version 2.0 MIME-Version: 1.0 Content-Type: application/ms-tnef; name="win...

visio version 5.0- how to make compatible with xp
I have visio version 5.0 and when I loaded it, it tells me "not compatible with this version of windows. Is there anyway to make it compatible with xp? Ignore the message. John... Visio MVP Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm Need VBA examples? http://www.mvps.org/visio/VBA.htm Common Visio Questions http://www.mvps.org/visio/common_questions.htm "Ron" <machinist314@comcast.net> wrote in message news:7B76942E-EF8D-460F-BF86-1216DB3E9EEA@microsoft.com... > I have visio version 5.0 and when I loaded it, it tells me "not compatible w...

Change link between form and subform
I have a form with a subform in it. I would like to change the way they are linked so instead of linking from Old ID, they link to New ID I don't know anything about code, is there a way to just change the cell it relies on? Thanks C Confused87 - Bring up the properties of the subform, and change the values in 'Link Child Fields' and 'Link Master Fields' on the Data tab of the properties dialog box. Make sure you have the subform selected, not the form within the subform. -- Daryl S "Confused87" wrote: > I have a form with...

Forwarding rule doesn't work, Outlook 2002 & Exchange 5.5
My company's email is hosted on an Exchange 5.5 server. In my Outlook 2002 (Office XP) client, I have set up a rule to forward all inbound messages to an external email address. This rule is only triggering, however, on internally generated messages such as Outlook meeting invitations or McAfee Groupsheild email alerts. No other messages (internal or from the internet) ar being forwarded. Any suggestions would be appreciated. Thanks! ...

Run "Workbook Open" once
I have a template that when it opens it automatically runs a macro that allows me to type in a text box what I want to save the file as and then saves it in the directory where I want it saved. The problem is that once it is saved I don't want it to run the macro again when I reopen that saved file. Should I put in the code at the end some way to deactivate the macro so that it no longer automatically runs when opening that newly saved file? Thank you. David P. If, as part of your routine, you were to write the filename to a specific cell in your workbook before saving, t...

Office 2010
I am just starting Office 2010 Beta and workbooks are a crappy gray --how to change? Try posting to the Office 2010 forums: http://social.technet.microsoft.com/Forums/en/category/office2010 -- Susan Ramlet -- please reply to the newsgroup so all may benefit. "JohnBee" <JohnBee@discussions.microsoft.com> wrote in message news:A98B3E10-6538-48F7-9CF1-0089F1C10CBB@microsoft.com... > I am just starting Office 2010 Beta and workbooks are a crappy gray --how > to > change? ...

linking #4
I am trying to link and .slk file to a .xls file all the links appear to be updating but i keep getting a message that excel cannot update 1 or all of the links. Is this common when linking with an .slk because i have several linked wrk books and have never had this problem. thanks Dean ...

how do i copy a chart to another workbook
I just upgraded to Office 2007. In 2003 I was able to select move or copy on a chart and select copy and select another workbook, and copy the chart to that workbook. It doesn't work in 2007. What do I do? ...

Linked Forms
Hello, I am doing a project that requires two forms.. The first form contains the data for a business the second form contains data for the business owner... How do I link the two forms together...? Many thanks. Bob Send a common key piece of data from the first form (say the company name) to the second form page and include it in the second form as a hidden form field (then if using a database to store the results link with a relationship the 2 results tables by the common field) For form passing information see http://irt.org/articles/js063/index.htm -- ____...

[?] change Server Exchange 5.5
Hallo, I have an organization unit with 2 exchange servers 5.5, both with public IP in NAT (isa server). One of these servers is broken and I have to change it with a new server. So, I have installed win2000 server, exchange 5.5 (in the same O.U.) with sp4. Some questions: a) In Routing I have 200-300 domains; is there a way to copy and paste all of them in the new connector, or I had to manually write them? b) Is there a way to use pop3 without installing the Internel Mail Service? I have create a mailbox in the new server (without the Internet Mail Service), I have tested it: all is ok for ...

Links not linking
Hello I have written a fairly big spreadsheet linking through the pages with SUM, SUMIF and SUMPRODUCT formula's What I am now finding is that when I update one page it doesn't update the rest, even if I am only typing in a figure to the SUM function. I have check and the calculations function is on automatic. is there a fix or something that I could run to make sure that all the formulas are working correctly. thanks Just a guess (since you already checked tools|options|calculation tab). How about selecting all the cells (ctrl-a (twice in xl2003)) and then edit|replace what: ...

Linked Table Manager in ACCESS
Hi, I am trying to change a field in an ACCESS table and get an error message that says the table is a linked table and fields can't be changed. After googling for some answers, I think I should be able to find out the link using "Linked Table Manager" in ACCESS. However, the "Linked Table Manager" button is grayed out. Any ideas/suggestions are welcome. Thanks. Richard Open the table in Design View. Reduce the window so that you can see the window's top bar. Right click in the top bar of the window (usually blue in color) and select ...

lookup #5
the Max() function will allow me to capture the maximum value in a column of data. Is there a similar function for text? Example: I have a Projects Status file that contains varioius data about the project and a color code to provide a "stop light" indication of status. Project ABC consists of 4 ecp that are variously G(reen), Y(ellow), R(ed). At the ABC level I want to automatically show the worst level of status - in this case R, because ecp3 is R. If ecp3 goes to G, then the rollup value would be Y because ecp2 is Y etc. Project Status ABC R ecp1 G ...

Exchange 2003 SP1 #5
Hi. Where can I check whether the SP1 is installed on my Server or not? When running the Windows Update, it doesn't suggest any Updates for Exchange?! Thanks Stefan You can tell by looking in Exchange System Manager. For example, in ESM I display things by Administrative Group so I go to the Server folder under Administrator Groups > First Administrative Group. The panel on the right will list the name of the server(s) and what the version/service pack level is. "Stefan" <news@stefan-strobl.de> wrote in message news:e33936f6.0412230106.40d7a87e@posting.google....

Link To A Cell From Chart
Hi all. I have a text box in a chart worksheet. Can I link it to an information from any cell in other worksheet in the same workbook. Thanks. Yes. Click the text box icon, then click on the chart sheet to insert the text box. Click in the formula bar and =Sheet1!A2 (or whatever cell you want to link). -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Salza" <salza@tm.net.my> wrote in message news:3fbfb0bf_2@news.tm.net.my... > Hi all. > I have a text box in a chart worksheet. Can I link it to an information from > any cell in other worksheet in...

links
Dear All, It is very critical for my business to learn the basics and the backbone of links in Excel. Are there any tutorials or articles that gives wealth of information about MS Excel links? (in Excel 9.0.6) Web addresses are also welcome. You can also post to my e-mail above. Thank you in advance. Mustafa .. I would advise you go to the newsgroup "microsoft.public.excel.links", and read everything you can about their troubles there and the solutions......... Vaya con Dios, Chuck, CABGx3 "Mustafa" <anonymous@discussions.microsoft.com> wrote in messag...

Linked Tables Over A LAN
Hi, I have a problem with a PC that is sharing an Access database over a LAN. I'm hoping someone may be able to give me a little advice. By the way, I'm a bit of an amatuer so go easy on the technical terminology ;-). I've got four PCs networked through a router which provides internet access. Two PCs are running XP Pro and two are running Vista Business 32bit. One Vista machine holds my full database while the other PCs have a similar database but with tables linked to the first machine. Been running this setup for several years, on various older PCs, with no problems. My proble...

Links
Every time I open a spesific workbook, I get the question if I want t use the old or the new data. This is very irritating! How do I disabl the link that is the reason for this message??? Please help me befor this drives me CRAZY!! ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Siri You will have a formula somewhere within the wordbook that is linked to another workbook. You can look for them manually and the copy>paste special>values... to kill it. or you could d...

email links in Publisher pdf
Why won't Publisher 2007 convert my email links correctly when saved in pdf format? It puts "mail to:" in twice automatically. It is converting website links without a problem. If memory serves the Office 2007 SP1 fixed this in Publisher. The SP2 is also now available. There have been some reports of not being able to open existing Publisher files after installing it, and a report that a fix for that bug is due by the end of the month....you might want to wait to install SP2 until after the first of the month, or just install SP1. DavidF "Rora" <Rora@discu...

Chart template and links
Hi, I have something "strange " (at least for me) going on ... I have an xls workbook, containing some data sheets and also 3 chart templates. These chart templates are copied via a macro to the output xls with the statement : Sheets(ChartName) _ .Copy Before:=Workbooks(targetfile.Window).Sheets(1) When I now look to this chart template in the output xls I got a link to the original xls workbook in which the macro is running. (I can see that via EDIT>LINKS...) Now I do NOT want that link to the originator xls workbook at all ! Is there anybody who knows how I can co...

Too many different cell formats #5
I have one worksheet with this problem but in despite of=20 doing a lot of modifications, some time after the problem=20 is still happens. Someone know how can I efficiently fix=20 this Problem? There is any tools to help me to repair that=20 even counting up how many formats I have, best regard Andr=E9 Martins Hi for dealing with too many cell formats have a look at the following KB article http://support.microsoft.com/default.aspx?scid=kb;en-us;213904 -- Regards Frank Kabel Frankfurt, Germany Andr´┐Ż Martins wrote: > I have one worksheet with this problem but in despite of > doing a ...

Linking to ODBC Linked Tables
Hi: I wonder if you have any suggestions for this. We have moved our Access tables into Oracle and have created a MS Access Database that links to these ODBC tables. Each of our users has a local copy of our Reporting Database that formerly linked to Access tables, but now we want them to link to the Ms Access Database which contains the linked ODBC tables on our shared drive. In trying to link to already linked ODBC tables, Access gives an error and will not link to these tables. Is there a way to let our users link to the Access database that contains the linked ODBC tables? Any...

Linked Graphs from Excel
I have linked graphs from excel into my publisher document using Paste Special. The problem is that when they paste in they reshape (I have pie charts and they come in publisher looking like tall skinny ovals). I can fix the shape of the charts manually after pasting them in, but when I update the links the reshaping reverts to its original, incorrect size. Any suggestions on how I can use the linking to excel and not have to resize every time I update? "Paste Special" as what format? Have you tried an image format? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http...

linking subforms
my application have customers and invoices. each customer can have 1 or more invoices. on my form i have a subform for customers and one subform for invoices. both subforms are in datasheet view. if i click on customer A in the customer subform i want the invoice subform to show only invoices for customer A and at the same time allow me to add invoices for customer A. what are the steps to accomplish this? also i want to be able to print a report such that customer A is listed together with all customer A's invoices followed by customer B and so on. how can this be accomplished? thanks ...

Copy Sent Messages V5.5
Exchange server 5.5 windows 2k Server If User A sends a message I would like a copy of that message sent to User B. Can this be done server side or does this have to be done within the Rules wizard on the client machine. are these sent internet or just intranet? internet you can store all in and out meesages then read them with a third party program. "Rob" <Rob@discussions.microsoft.com> wrote in message news:FF02B272-700E-4927-AD41-1DF097E56A59@microsoft.com... > Exchange server 5.5 > windows 2k Server > > If User A sends a message I would like a copy of th...