Excel 2007 : some links not updating.

**fireworks** · 04-18-2009, 09:16 PM

I have a linking problem that I need help with desperately. These spreadsheets run my company.
I am finding some of my links are not updating! I have to go through the copy and paste link process all over again to get some of them to work. Please help!!!

Mike

**broro183** · 04-19-2009, 08:43 AM

hi Mike,

I don't have access to Excel 2007, but if I considered this problem in Excel 2003, the things I would check first are:
*) Tools - Options - Calculation & check that "Automatic" is selected.
*) Edit - Links & select all the files listed and press "update". Also have a look at the updating options near the bottom of the dialog box.
*) check that the cells are not formatted as text.

hth
Rob

**fireworks** · 04-19-2009, 04:05 PM

Thanks for the response Rob,
The equivalents to those items in'07 were ok. I found out I had to open both the source and destination sheets to make the link work. I'm going to do more observation but I don't remember having to do that in the '03 version.

Also really having a problem with the row or column selection not being highlighted enough to tell unless my nose is about 2" from the screen. I can't find a selection anywhere to change that and I find from the forum here that I am not alone.

**broro183** · 04-19-2009, 05:09 PM

hi Mike,

If your formulae use the Indirect function (& possibly this affects other functionss?) then both files need to be open in Excel 2003.

Excel 2003 Help Files state:

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

Yes, I've seen that "you're not alone" as well & I think I've seen some solutions (within Excel) explained but I can't remember them. However, I have seen some highlighting code which may be of interest, for example: http://www.mcgimpsey.com/excel/highl...iteRetainColor

hth
Rob

**fireworks** · 05-03-2009, 11:57 AM

Thanks for the input broro183,
Found that in the 2007:
Office Button, bottom right, -Excel Options
-Trust Center
-Trust center settings
-External content
-security settings for workbook links
"enable automatic updates for all workbook links," It says (not recommended).
Also, the ribbon under the quick access toolbar: Data tab - connections group - edit links - update shows a selection of automatic or manual.
Looks like this updates automatically now.

Also, under Vista the Excel 2007 problem of the row and columns not highlighting has been corrected.

**broro183** · 05-03-2009, 04:47 PM

Awesome - thanks for posting the solution, others may be able to use the thread to resolve their own issues :-)

Rob

Edit: Can you please mark this post as solved?