Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Link path not updating when renamed

We have a process by which we open a (multiple sheet) Excel workbook, then
open a Word document with links to the Excel file. We then rename the Excel
file (to a different path & filename (everything is on the same machine)) and
check that the Word links have updated correctly. This system works perfectly
in Office 95.

Since updating to Office 2003, however, we only have the links to the
CURRENTLY OPEN Excel sheet which update to the new path and filename. All
other links still point to the original (pre-renamed) path & filename.
Why do the other sheets not update??

Any help appreciated


Post your answer or comment

comments powered by Disqus
We have a process by which we open a (multiple sheet) Excel workbook, then
open a Word document with links to the Excel file. We then rename the Excel
file (to a different path & filename (everything is on the same machine)) and
check that the Word links have updated correctly. This system works perfectly
in Office 95.

Since updating to Office 2003, however, we only have the links to the
CURRENTLY OPEN Excel sheet which update to the new path and filename. All
other links still point to the original (pre-renamed) path & filename.
Why do the other sheets not update??

Any help appreciated

Hi,

I recently migrated to 2003 and when using a file created in 2000/2002 the
links
do not update when the Source file is open. First, I opent the destination
workbook, choose not to update links, open the source workbook and make
changes. The changes are not updated in the destination workbook.

If I edit the equation in the destination workbook by deleting the path
('serverfolder[filename]sheet'!cellref) up to the filename of the source,
then the values update. Once I close the source, the equation in the
destination changes to a mapped drive type path
('S:folder[filename]sheet'!cellref).

Why are the links not updating when the source is open, and why is the path
changing from server style to mapped drive style?

I have Sheet1 linked to Sheet2. The Sheet2 is linked to the Sheet3.
I want to update values in the Sheet1 linked to the Sheet2 assuming if any
changes are done in the Sheet3 it'll be reflected in the Sheet1.

So, I'm changing the values in the Sheet3 and closing it. After that, having
Sheet1 opened I'm openning the Sheet2 and everything is fine the values in
the Sheet1 have been updated.

But, when I'm clicking a button on the Sheet1 and using the code below it's
not working - the values in the Sheet1 are not updated when I'm doing changes
in the Sheet3 and closing it and after that openning the Sheet2 through the
code below with supposed to be links updated.

Can anybody help me with it.

Sub Update_Links()
On Error GoTo Update_Links_Err
Dim xlapp As Object
Dim xlbook As Object
Dim strFileName As String
Dim logFlag As Boolean

logFlag = False

strFileName = "Sheet2.xls"

Set xlapp = CreateObject("Excel.Application")

xlapp.DisplayAlerts = False
xlapp.AskToUpdateLinks = False

Set xlbook = xlapp.Workbooks.Open(strFileName, UpdateLinks:=1)
logFlag = True

xlapp.AskToUpdateLinks = True
xlapp.DisplayAlerts = True

xlbook.Close savechanges:=True
xlapp.Quit

Set xlbook = Nothing
Set xlapp = Nothing

Update_Links_exit:

Exit Sub
Update_Links_Err:
xlapp.DisplayAlerts = True
xlapp.AskToUpdateLinks = True
If logFlag Then
xlbook.Close savechanges:=False
xlapp.Quit

Set xlbook = Nothing
Set xlapp = Nothing

End If
MsgBox Err.Description
Resume Update_Links_exit
End Sub

Good day,

I have observed an interesting problem with Excel 2003, in that the
excel charts (any type) do not update when the source data is changed
when one has a large excel workbook (I am currently at 160 MBytes).
This is a new behaviour that appeared when I surpassed 65,536 or more
dependencies to unique references (the level or number of links between
formulas).

Any ideas on how to fix this would be greatly appreciated.

Thanks,

James

Hello - My co-workers and I have been having a lot of problems with some of our Financial Model's we've setup in Excel 2003. It use to be that if we have several workbooks open and one being the source file. If we made changes to the source file, the linked cells from the 2nd and 3rd files would automatically update. Now it doesn't automatically update anymore. To resolve this, my co-workers will have to click on the linked cell from the 2nd and 3rd workbooks and either Relink it to the source file or just hit "enter". After they've relinked the cells, it will automatically update for them in the future. However, if I were to open the same set of workbooks..source, 2nd, and 3rd files, I will experience the same problem of linked cells not updating. So if I relink the cells and save it would work for me in the future, but then the next time my coworker opens the file, they will have the problem of cells not updating. The files are located in our Work server, and we access the files from our desktops at our desk. None of us have recently modified our user settings of whether or not Excel should "prompt" us to update linked cells. We've always said no, and linked cells would refresh either on their own or when we've open the source file. Now even with source file open it won't update. It doesn't happen to every linked cell in the workbook, nor is there a specific workbook that has problems. Anyone have ANY idea of what the problem can be?

I have several good size excel workbooks with 30-40 charts with 5-10
series per chart which do not update when the workbook is recalculated.
I have written a macro to select each series in each chart and redefine
the x axis values (but not changing it) - rather brute force as you will
see - so that the charts can be easily ensured to be updated. Is there
an easier fix than this? We are running excel 2003.

rather than 100's of lines of code like the following, can this be done
via something like

for each chart....
for each series....

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "='Weekly
Data'!R1C3:R1C94"
ActiveChart.SeriesCollection(2).XValues = "='Weekly
Data'!R1C3:R1C94"

once is was set up it ran fine, but adding a chart or series was a
pain.

thanks in advance.....

--
duane

------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=496294

I have a chart embedded in a worksheet and when the data that populates the
chart is changed, the data displayed on the chart does not change. We
saved the workbook, closed it and reopened it and all was good with the
world. Can someone explain to me why the charts are not updated when the
data is changed?

Thanks,
Barb Reinhardt

I have several good size excel workbooks with 30-40 charts with 5-10 series per chart which do not update when the workbook is recalculated. I have written a macro to select each series in each chart and redefine the x axis values (but not changing it) - rather brute force as you will see - so that the charts can be easily ensured to be updated. Is there an easier fix than this? We are running excel 2003.

rather than 100's of lines of code like the following, can this be done via something like

for each chart....
for each series....

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "='Weekly Data'!R1C3:R1C94"
ActiveChart.SeriesCollection(2).XValues = "='Weekly Data'!R1C3:R1C94"

once is was set up it ran fine, but adding a chart or series was a pain.

thanks in advance.....

Hi,

I recently migrated to 2003 and when using a file created in 2002 the links
do not update when the Source file is open. First, I opent the destination
workbook, choose not to update links, open the source workbook and make
changes. The changes are not updated in the destination workbook.

If I edit the equation in the destination workbook by deleting the path
('serverfolder[filename]sheet'!cellref) up to the filename of the source,
then the values update. Once I close the source, the equation in the
destination changes to a mapped drive type path
('S:folder[filename]sheet'!cellref).

Why are the links not updating when the source is open, and why is the path
changing from server style to mapped drive style?

I have the same question as described in this post; however, the poster did not reply to the request-for-clarification questions.

My chart does not always update when my source data changes. My Calculations setting is Automatic (not Manual). There is no macro code working to produce the source data or the chart. My chart is on the same worksheet as my source data. The source data is simple links to other cells in the same workbook that update as the underlying data is updated. Saving doesn't work. Manually calculating (F9) doesn't work.

The only way I have found to get the correct data to display in the charts is to open the Source Data and re-enter the Series range. Obviously, that gets old.

I have searched several posts and cannot find the solution - Thank you very much in advance!

I've come to feel like Alice in Wonderland. I have several worksheets which have simple links to other worksheets from the same workbook and other workbooks. The cells in these worksheets do not update with autocalc on or when I press F9. However, cells update when I hit F2 and then exit the cell.

I have other worksheets open at the same time linked to the same sources that update correctly. I've tried refreshing links and closing and reopening the worksheets. Could these sheets be corrupt? If so, what would be the best fix? Thanks for any help.

Steve

Hi

I am working with three excel workbooks where data flows between them. For some reason, the workbook links aren't updating, when they should. This problem is intermittent, some times they work, sometimes they don't. Note the calculations within each workbook are being performed, it is just the external links that aren't.

I have:
- checked and re-checked the links and know that they are pointing correctly between workbooks
- Made sure I have calculated the workbooks
- set automatic update of links on
- checked links via Edit Links, check status and it says Source Is Open
- even changed the source file to re-establish the links, then update values, and again status is Source Is Open

But no joy.

Were I to go to an actual cell that contains an external link that isn't updating, press F2, then enter, it calculates the result and it updates fine. The values will also update when I shut down a workbook and re-open, or if I press Shift+Ctrl+Alt+F9, it will do it, but not during a normal F9.

Has anybody ever come across this before?

I think it is highly likely that it is due to the file size. The files are very big, one is 70 meg, the other two are around 30meg each.

The external workbook links are a tiny fraction of the total number of calculations in the model. I have limited them as much as possible. But I think it is the sheer number of calculations in the whole model that the computer can't keep up with. But if this is the case, why will the internal worksheet calculations be calculated, but the external workbook links not?

I'm workin on a laptop with 4gig of RAM and 2.4Ghz processor speed so should be able to handle it.

Any thoughts v much appreciated.

Cheers

Chris

I've got a workbook with multiple sheets in it. The first one is a huge file
(35 columns and 1,500 rows) with complex functions. Subsequent sheets are
linked to the first. To illustrate my question, I'm using a simplified
scenario:

-Sheet 1 is the "Master" that has sales data: Name, Month, State, Dollar Amt.
-Sheet 2 is linked to sheet 1 and picks up the CA sales data directly (ie: I
went into Sheet 2 and typed =sheet1!a1, etc. for all the CA sales data in
Sheet 1)
-Sheet 3 is also linked to sheet 1 and pickes up sales data from another state
-Sheet 4, ditto for another state.

My problem is that when I sort the data in Sheet 1, my links do not follow
my data, rather they continue to reference the linked cell. (If I have a
link in Sheet 2 to =Sheet1!A1, when sheet 1 is sorted, that reference stays
the same although the data is now in cell A10.) My linked references are not
absolute, so I'm not sure why the links are not following the data.

Hope someone can help! Thanks!

--
Susi

Excel 2007 for files saved as .xlsm (macro) will not issue the message "links
to ##### were not updated because ##### was not recalculated before it was
saved" Is this working as designed? Documentation seems to indicate that
message should still be received, but it doesn't identify specific file
types. Is there a set up change required to activate this message?
Steps to create
Create a Detail workbook.xlsm and a Summary workbook.xlsm. Link data in the
Summary to the Detail. Turn on manual calc & no calc on save in both files.
Calc and save files. Open Detail workbook and make a change to a number, save
without calcing workbook. Open summary (while detail is closed) click on
security message, click option to enable links...the "not updated
because...not recalculated" message is not received. Also click on Edit
Links, click to update values, link displays as Ok, instead of displaying
error message.
Thanks in advance for assistance or advice.

When I open a worksheet, the purpose of this worksheet is to summarize and
calculate 30 or so other worksheets. Each of the worksheets are linked to
the summary worksheet. Excel does not update the other worksheets. When I
check the status of the links, Excel tells me "Error: Sourse not Found", but
when I click on the "Open Source" button, the file opens, and Excel will
update the worksheet. Once I close the worksheet, the summary worksheet no
longer recognize it. Any ideas of whats going on here? I am using Excel
2003.

I have a workbook with about 125 tabs/sheets - each tab/sheet with several
columns that are summarized at the bottom, and then those summarized numbers
are linked to a main "summary" page. I noticed today that when I changed
numbers in any of the columns in any of the sheets, the autosummed total at
the end of the column did not change. I checked the formula in several
different columns, and they are all right, it's just not updating. I
re-entered the autosum formulas, and then it would calculate the right
numbers, but if I changed it again, it would again, not update. Also, none
of the sums of the columns are updating to the main summary page, even though
those formulas are correct too. This problem is throughout every sum and link
in the entire workbook. Does anyone out there know the fix for this? I have
looked and looked, and am completely stumped. Thanks in advance for any
suggestions.
-Angela

I have searched the board, google & Microsoft and can't find the answer, please help if anyone knows.

I have a model that has links to another workbook (call it source.xls). When both workbooks are open and source.xls is updated and saved, the model does not update with the new information. To get the information to update, I have to activate the formula and click enter.

I have my options set to Automatic calculation and to Update Remote References.

Anyone know what causes this and how to fix it.

This doesn't happen with every file that I have links for, it is somewhat random...

I keep getting a message when attempting to open a linked worbook that the
links were not updated because the file was not recalculated prior to saving.
I really don't want to update the file before saving because it opens
read-only and I refresh the data anyway once it is opened. This message is
interupting a macro I am running to perform the tasks. What is the code I
need to enter in the macro to say "OK" to the question it asks as to whether
I want to update links with the current data? I have "UpdateLinks:=0" in the
macro when the file is opened but that does not help. Thanks.

Excel files with external links do not update automatically. Have followed all the instructions:
File - Options - Advanced - When calculating this workbook = Update links to other documents unchecked, as recommended in Help. Even though intuitively one would think this has to be checked.

Data - Edit links = Automatic.
Startup prompt = Don't display the alert and update links.

Still not updating automatically. Didn't have that problen in Excel 2007. Help.

I have a fairly complex workbook that hasn't had any macros up till now. I'm running a macro that, within a loop, changes a cell value, and then copies the value of another cell (that contains a formula) into a seperate sheet.

The problem is that when running the macro, the cell containing the formula is not updating for each iteration of the loop. I'm sure it's pretty simple to fix, but I can't find the right part of the help documentation.

Many thanks for your help with this.

Hi,

For some reason, the charts in my excel workbook do not update automatically when the data are changed. I have set Calculation to Automatic, but it still doesn't work. They update only if I close and reopen the workbook. Is it due to a problem with setting? How can I get the charts to automatically update?

Thanks

Hello everybody,

For some reason, my calculations are set on Automatic but some cells B that are linked to cells A do not update when cells A change.

Do you have an idea why ?

Thank you for your help,

Thomas

I have written the following VBA function


	VB:
	
 Eff(CL) 
     'Aerodynamic Efficiency
     'Function of CL
     'unitless
     'E=L/D or CL/CD or CL/(CD0+k*CL^2)
     
    AR = Range("AR").Value 
    CD0 = Range("CD0").Value 
    e = Range("e").Value 
    x = 2 
    Pi = Application.Pi() 
    k = 1 / (Pi * AR * e) 
    Eff = CL / (CD0 + k * CL ^ 2) 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Where AR, CD0, and e are all assigned values i put in excel using Name Manager.
The function works and everything, the only problem is that if I change the values of AR, CD0, or e in excel, the function does not update itself with the new values and displays the values using the old parameters. Is there a way I can have the function auto recalculate / auto update when the values of AR, CD0, or e have been changed in excel?

ps.
I have tried defining AR, CD0, and e in the code itself and changing it from there but it is not as efficient as if I had those values in excel. In excel I can add a scroll button / slide bar to allow for easy changes in those values. So if possible I would still like to keep the values of AR, CD0, and e defined in excel

also ive included my excel fileefficiency.xlsm

My large workbook that has links all over the place to tabs within the
workbook is not auto calcing/updating links. The auto calc feature is on in
the Options/calculation menu. each cell i have a link to shows a live link
but the link is not working


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