Free Microsoft Excel 2013 Quick Reference

Updating links to closed workbooks

I have a report template full of formulas. I'm using a variety of
Index/Match formulas to find 2 criteria and return the desired result. I'm
also using Sumproduct to sum columns based on 2 criteria. As you would
expect, with these array formulas it takes a while for Excel to perform all
the calculations. There are 4 workbooks that I'm saving over each week that
supply the data for my template report workbook. If I save over these 4
workbooks, then open them up, then open up my template, it takes the template
about 6.5 minutes to update. This isn't too bad. What I would like to do is
this:
Save over the 4 workbook files. Then just open the report template and
select update links. When I tried to do this yesterday afternoon, I hit
update links at about 5 pm and left for the day. When I came back in this
morning, the screen on my computer still had the update links box up...it
looked like no progress was made and Excel was frozen. I tried again this
morning and the same thing happened. The report template and the referenced
files are all in the same folder on a network drive. Is there a reason why I
have to have all the files open in order for the template to update? Why
won't it update the closed files?

Thanks,

Jeff


Post your answer or comment

comments powered by Disqus
XL2000: Links to Closed Workbook with Defined Names Update Slowly

248207 (http://support.microsoft.com/kb/248207/EN-US/) XL2000: Linked Files with Lookup Functions Updated Much More Slowly Than in Earlier Versions of Excel

My colleague is having trouble linking to another workbook, which he wants to
remain closed. The link looks like this:

=-SUMIF('G:Master Appraisals & CashflowsBLEL proformas[BLEL - Northampton
J16 M1.xls]Array List'!$B$3:$B$26,"=01/08/2007",'G:Master Appraisals &
CashflowsBLEL proformas[BLEL - Northampton J16 M1.xls]Array
List'!$C$3:$C$26)

Trouble is, we have around 16 workbooks we're linking in this way, so
amalgamating is not an option.

Can this work with workbooks when they remain closed?
Any help appreciated. Thanks, Greg

Hi all,

I have a spreadsheet which contains links to other workbooks. Obviously when I open it it asks me if I wish to update these links. However, the spreadsheet is part of a report which I want to run automatically every night so I would like a way to write into the VBA code that when the workbook opens to automatically update these links and not ask me. Does anybody know of a way to do this?

Thanks,

Ben

Is there a way I can make this formula work:

=SUMIF('[Video Tally Sheets-Oct 07.xls]NU download'!$A:$A,'2007'!$B4,'[Video Tally Sheets-Oct 07.xls]NU download'!$G:$G)

Currently, the only way it works is if I have the Tally Sheet workbook open. If it is not open I get Value errors.

I know I have many other worksheets linked to closed workbooks, but I wondered if it has something to do with the SUMIF portion. Not sure how to fix it.
Thanks for your help.

Hello,

I just spent the entire morning linking these two workbooks together. I've named some cells in the source workbook, and linked these names to the destination workbook. Then I used the names in some formulas in the destination workbook, and it worked great. But when I closed the source workbook it doesnt work. WHen I open the source it works great, but I need for it to be able to link to the workbook when it is closed as well. Does anyone know what I am missing? thanks for your help.

To all

I am trying to figure out the VBA code, which would disable the pop up dialog box prompting the user to update links on opening the workbook, yet updates the links automatically.
1. I am trying to use the Application.AskToUpdateLinks=True, in the Workbook_Open() event, which should be straightforward but still on opening the workbook by double clicking on it prompts the dialog box to popup

2. the next approach was to use:
ActiveWorkbook.UpdateLinks=xlUpdateLinksAlways (available only in office 2003) but it doesn’t do the task as well.

This should really be straight forward tho… anyone knows better? Or am I lil drunk

Thanks

hiya, i have a vlookup to another workbook that's closed, the full path and name is in the lookup. What would i write in workbook_open to automatically update the values so the user doesn't have to click on the message that pops up when the file is opened?

My sumif formulas containin links to other workbooks do not calculate. Once
I close the source workbook, the formula results turn to "#value!" The
source workbook is listed under links in the destination workbook. I have
other links that are not in a formula and they are updating correctly. The
source workbook is called Test and and the data is on sheet1. Here is the
formula in my destination workbook: =SUMIF('C:Documents and
SettingsGrantMy Documents[Test.xls]Sheet1'!$A:$A,A3,'C:Documents and
SettingsGrantMy Documents[Test.xls]Sheet1'!$B:$B)

Any suggestions?

I have an excel spreadsheet with formulas linked to data in another spreadsheet. The spreadsheet with the source data is not opened. When I press the Update Link button, the cells with the formulas linked to the closed spreadsheet returns a #VALUE# indicator. I have to open the source spreadsheet so that the Update Link will return the correct data. Why doesn't Excel extract the data from the source spreadsheet when it is closed?

Hello,

I just spent the entire morning linking these two workbooks together. I've named some cells in the source workbook, and linked these names to the destination workbook. Then I used the names in some formulas in the destination workbook, and it worked great. But when I closed the source workbook it doesnt work. WHen I open the source it works great, but I need for it to be able to link to the workbook when it is closed as well. Does anyone know what I am missing? thanks for your help.

My sumif formulas containin links to other workbooks do not calculate. Once
I close the source workbook, the formula results turn to "#value!" The
source workbook is listed under links in the destination workbook. I have
other links that are not in a formula and they are updating correctly. The
source workbook is called Test and and the data is on sheet1. Here is the
formula in my destination workbook: =SUMIF('C:Documents and
SettingsGrantMy Documents[Test.xls]Sheet1'!$A:$A,A3,'C:Documents and
SettingsGrantMy Documents[Test.xls]Sheet1'!$B:$B)

Any suggestions?

If you open a spredaheet and are presented with the window 'This book contains links to another workbook, do you want to automatically update the other workbook' - is there a quick way to find where the links are? Basically I have a workbook of 55 sheets, each with lots of different formula. None of it should be linking to another workbook so I need to find out where it is and amend the formula but was wondering if there was a quick way as it could be in any of the sheets??

Thanks

Andie

Hello

A while ago someone told me about a clever little tool that could find and delete the links to another workbook. I have a large workbook that has suddenly developed a lot of problems and I don't know why. One of them is on opening I get the message 'this book contains links to another workbook, do you want to update the other workbook' message. it shouldnt contain any links, trouble is I dont know where the links are and the workbook contains 45 sheets all with lots of formula so the links could be anywhere. Can anyone tell me where the tool is, I downloaded before as it doesnt seem to stay in excel, it seems you can only use it once each time.

Many Thanks in advance.

Hello, all, and a good day to you! I would like to consult with you, to see if I can obtain an answer to my small problem.

I have an Excel workbook which only has two sheets, with some of the cells having a formula to copy a cell from the other sheet. Everytime I open it, I get an opening message:

"The workbook you opened contains automatic linkes to information in another workbook. Do you want to update this workbook with changes made to the other workbook?"

I don't know what "another workbook" the message refers to. Is there a method of determining its name?

What I would like to do is to break this apparent link to another workbook, as my present workbook should be totally self-contained.

It could it be that, at some time, I copied something from another workbook, and, so, established the link If that is my situation, is there a simple method of determining what cell(s) are so linked, as well as the name of the other workbook? Or, if the name of the other workbook can be determined, is there a way of easily finding which cells are linked to that workbook?

Hi All,

I'm new to the website, but I was wondering if anyone could help.

Basically I've used SUMIF to extract information from a workbook stored on out Q: drive according to a criteria and save it in a new workbook in our F: drive. When both files are open the formula works fine linking to the workbook in Q: and extracting the informaion required, however as soon as I close everything and open my new workbook again, it returns #VALUE!!. Its as if it forgets where the link goes to. When I open the origonal Q: workbook it remembers again and returns the correct value.

The problem is, I've got a different workbook for everyday from the last 3 months to extract the informaion from and I don't really want to have to open each of them every time I open the spreadsheet!

If there is no way round this, is there a way I can save just the values and not have to use the links after the first time?

Many thanks for any help anyone may be able to offer

Cath

I have a spreadsheet that I inherited and have made several modifications and enhancements to. When I open this file, I'm prompted with the message saying "This file contains macros..."
1) Is there code I can implement that will effectively disable this message from popping up?

I also get a message saying that there are automatic links to other workbooks, do I want to update the changes to this workbook with changes made...

2) What is the best method of finding the links? To my knowledge and research, there are no links to "other workbooks." Can I use project explorer to find it?

When i open my workbook that has links to another workbook it asks to update. When i select yes after a good 5 minutes (dont think it should take that long) it returns N/A#. The formulas are using a =sumif(from other workbook,this workbook,from other workbook). However as soon as I open the other workbook everything automaticaly updates and is fine. Any ideas why?

Thanks!

I have a summary workbook which links to a bunch of cells in an external
workbook. The external workbook's name is "01 04 2007 GDCS forecast & TCAPS
analysis"

However, every time this external workbook is modified, I like to change its
name to reflect the most recent update; i.e., if I were to modify the
workbook today I would change its name to "01 09 2007 GDCS forecast & TCAPS
analysis", etc.

Therefore, I'm wondering if there is a way (a macro, probably?) for the
workbook that links to this workbook to automatically change its formulas to
reflect the new workbook name? Hope that makes sense? I know I can do this
via the Edit--Links menu path but the person using this summary file is not
that expert with Excel, and I would prefer such updates to happen
automatically, if possible.

Dave
--
Brevity is the soul of wit.

I have two workbooks that are linked. When I open one, I am given the
following message "The workbook you opened contains automatic links to
information in another workbook. Do you want to update this workbook with
changes to the other workbook?" When I click "yes" it prompts me to open a
workbook that is no longer linked to this workbook. I have tried to find
where the link to this file exists and I cannot. How can I see what
workbooks are linked to a file? How can I break this relationship if it
should no longer exist?

Hi,

I have a problem with making charts linking to external workbooks.
Let's say I wan't to make a couple of charts in workbook
"ChartBook.xls", that uses data from linked workbook "DataBook.xls".

I have for example made a named range called "AHeader" defined like
"='D:Data[DataBook.xls]Sheet2'!$A$1".

If I use the named range in a field in ChartBook.xls like "=AHeader"
it's fine and shows the correct text even if "DataBook.xls" is closed.

But if I make a chart in "ChartBook.xls" and insert "=Sheet1!AHeader"
in a name-field for sourcedata, I get an error about that the reference
cannot be found, but this only happens if "DataBook.xls" is closed, when
"DataBook.xls" is open everything works fine.

Can somebody please help? Thanks!

--
WitchMaster
------------------------------------------------------------------------
WitchMaster's Profile: http://www.excelforum.com/member.php...o&userid=27996
View this thread: http://www.excelforum.com/showthread...hreadid=474988

I have a workbook that is linked to another workbook, but I have included a
formula at the beginning to ensure that no errors are returned
(If(iserror(linked workbook A1 + Linked workbook B1),"",linked workbook A1 +
Linked workbook B1))). This works fine when both workbooks are open, but
when I attempt to open the linked file on its own it doesn't appear to update
the information. When I check the link status, it shows that it is ok.

Any help would be appreciated.

Thanks,
--
Drew

When I receive a excel workbook made by someone else and go to open it, a
yellow box appears that reads" There are automatic links to another workbook.
"Do you want to update this workbook with changes made to other work book?"
one then clicks "yes" or "no"
These are very big workbooks and I want to be able to find what cells have
the formula to look up inofrmation form another file. Although I can click
and read every cell that contains information (1000's of them) there must be
a much easier way. Any suggestions?
Thanks

I am trying to remove the startup prompt that asks if I want to update links
to external workbooks (I want the links to update automatically for this
workbook).

I clicked on Edit -> Links...
I clicked "Startup Prompt..."
I selected "Don't display the alert and update links"
I clicked OK, and closed the dialog box.

When I try reopening the workbook, the prompt still comes up.

Hi, this must be easy, but I=B4ve searched and can not find the
solution:

I have an Excel spreadsheet with realtime stock prices (spreadsheet A).
I have written a little tool in a separate file (spreadsheet B) that
links to the realtime prices and performs some checks on them.
However, while the macro is running through its user-specified amount
of loops, the data is frozen. How can I avoid this? Can I tell Excel to
keep updating the link from sheet B to sheet A, or how can I include an
update routine into the macro loop?

Thanks a lot
Kai


No luck finding an answer? You could always try Google.