Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
Links not Updating
Hello Excel Mavens,
I have worksheet linked to another workbook. I have an equation that pulls data from that linked workbook. When I open the workbook, the data does not get updated and I get the #VALUE error. If I open the linked workbook the data updates. Other linked equations (to the same workbook) refresh perfectly. The equation is:=COUNTIF('S:\[Nov 05 Recap.xls]Sheet1'!$E$7:$G$21,O4) This is Excel 2003 Pro (both workbooks) Any ideas would be appreciated. Brian |
#2
Posted to microsoft.public.excel.links
|
|||
|
|||
Links not Updating
Excel has difficulty executing some formulas with links to closed
workbooks. I can't say I am surprised since it must have to do a lot of work to determine the contents of the cells without fully opening the workbook concerned. Ways in which you might be able to help it: - open the file<g (you could do that by macro on opening the destination file, and you could open it read-only to reduce the interaction with other users) - include the formula result in a cell in the source workbook and just reference that single cell in the linked formula - use a named range in the source workbook rather than an address like $E$7:$G$21 (I'm not sure this helps, but it might; it certainly makes maintenance simpler because you can insert rows and columns in the source workbook without having to remember to have the destination book open at the time). Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#3
Posted to microsoft.public.excel.links
|
|||
|
|||
Links not Updating
Thanks Bill,
Those are the exactly the things i thought i would try. I was hoping that I had missed something in the link settings (smile). I hate to use named ranges since my users are Excel nubs and barely understand the concept of linking. All the best from rainy Olympia, Washington Brian "Bill Manville" wrote: Excel has difficulty executing some formulas with links to closed workbooks. I can't say I am surprised since it must have to do a lot of work to determine the contents of the cells without fully opening the workbook concerned. Ways in which you might be able to help it: - open the file<g (you could do that by macro on opening the destination file, and you could open it read-only to reduce the interaction with other users) - include the formula result in a cell in the source workbook and just reference that single cell in the linked formula - use a named range in the source workbook rather than an address like $E$7:$G$21 (I'm not sure this helps, but it might; it certainly makes maintenance simpler because you can insert rows and columns in the source workbook without having to remember to have the destination book open at the time). Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating Links | Excel Discussion (Misc queries) | |||
Updating Links on Excel | Excel Discussion (Misc queries) | |||
Links not updating in shared workbook | Excel Worksheet Functions | |||
Updating links | Excel Discussion (Misc queries) | |||
Links updating? | Excel Discussion (Misc queries) |