External lookups won't load
 

I have a workbook that references information from three external workbooks
saved in different folders. When these external workbooks are closed the data
won't update, and when I try to force it to I get '#VALUE!' errors for all fo
the data it's trying to pull. However when I open the other workbooks then
the cells wil update like they should. I need to get it so the table will
update without having to open all of the files.

It may be because of the volume of lookups. There are 2000 rows with 3
columns doing a search through one column of about 1000 rows in the external
workbooks. It takes excel over a minute to go through all of the updates when
I can get it to work. Does Excel normally get buggy when dealing with large
ammounts of data?

Please post a sample formula.

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Paul Zipko" <Paul wrote in message
...
I have a workbook that references information from three external
workbooks
saved in different folders. When these external workbooks are closed the
data
won't update, and when I try to force it to I get '#VALUE!' errors for all
fo
the data it's trying to pull. However when I open the other workbooks then
the cells wil update like they should. I need to get it so the table will
update without having to open all of the files.

It may be because of the volume of lookups. There are 2000 rows with 3
columns doing a search through one column of about 1000 rows in the
external
workbooks. It takes excel over a minute to go through all of the updates
when
I can get it to work. Does Excel normally get buggy when dealing with
large
ammounts of data?

There are some functions that don't work with closed workbooks.

=sumif(), =countif(), =indirect()
are a couple.

But there are sometimes alternatives. You may want to post your formula.

Paul Zipko wrote:

I have a workbook that references information from three external workbooks
saved in different folders. When these external workbooks are closed the data
won't update, and when I try to force it to I get '#VALUE!' errors for all fo
the data it's trying to pull. However when I open the other workbooks then
the cells wil update like they should. I need to get it so the table will
update without having to open all of the files.

It may be because of the volume of lookups. There are 2000 rows with 3
columns doing a search through one column of about 1000 rows in the external
workbooks. It takes excel over a minute to go through all of the updates when
I can get it to work. Does Excel normally get buggy when dealing with large
ammounts of data?

--

Dave Peterson