Values won't update from external worksheet

[pmzipko]

I'm having trouble getting cell formulas to update when they reference
a saved workbook.
The situation I have has one workbook for each of 3 brands of hardwood
floors, and a fourth workbook listing trim and molding pieces to go
with those floors. The structure of the workbooks looks something like;

G:\
...Sales Admin\
|
|-BRAND 1\
| |-Brand1.xls
|
|-BRAND 2\
| |-Brand2.xls
|
|-BRAND 3\
| |-Brand3.xls
|
|-WOOd TRIM\
| |-Trim.xls
|

In each of the Brand spreadsheets there is a listing of floor products,
and a column in those products that lists the product code of the
particular piece of trim that is recomended for that floor. In the Trim
spreadsheet there is a list of trim products with about 1900 entries,
each with a unique product code in the first column. In the Trim
spreadsheet there are three columns that are supposed to check to see
which brand of floors that piece of trim is used with. The column looks
in one of the brand workbooks and checks the column of recomended trim.
If it finds the product code for that row then it puts an 'X'
indicating a match. The point of this is that each piece of trim isn't
brand specific, and can apear on one, multiple, or no floor worksheets.
Our users want to be able to sort and filter the trim table by these
brand columns to only get the products associated with the floors they
use.

The formula to preform this lookup looks like this;

=IF(COUNTIF('G:\...Sales Admin\BRAND
1\[Brand1.xls]Finished'!$L:$L,A3)>0,"X","")

'Finished' is the sheet name these floors are on, '$L' is the column in
that spreadsheet where the recomended trim is, and A3 is the cell for
this row that has the product code. If the product code appears one or
more times in column L of the floor table, count will be greater than
zero and the statement will print an X, ortherwise the cell will be
blank. There is a similar function for each of the three columns, and
this function fills down through all of the rows of the trim table.

The problem I'm having is that the function will only update if the
Brand spreadsheet is open. When I try to update the table with the
brand spreadsheets closed I get a #VALUE! 'A value used in the formula
is of the wrong data type' error for all of the lookups. Using the
Evaluate Formula tool the error seems to come from the CountIf call.
After that step #VALUE! is returned and used for the rest of the
evaluation. If I open up one of the Brand spreadsheets then the cells
in that column will automatically update themselves and replace the
#VALUE! error. The only way I can get the columns to update is to open
all three spreadsheets. I haven't had this kind of problem in the past
when trying to create links from one spreadsheet to another. The
problem might be just with the size of the tables. Originally this
update took 8 minutes to complete, and now with a more effective search
is down to 1 minute. I need to get this table to update automatically
without having to open all of the tables it pulls from. I haven't been
able to find a solution to this and would appreciate any assitance.

Paul Zipko

 

[pmzipko]

bump

Anyone know how refreshing data works if the source table is in a
seperate workbook that isn't open? (see above)

 

[Dave Peterson]

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

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

You could use an alternate formula that does work with closed workbooks, though.

=SUMPRODUCT(--('C:\My Documents\excel\[Book1.xls]Sheet1'!$A$1:$A$6="a"))

I'd open the workbook first--just so I wouldn't have to type the whole path.

=IF(SUMPRODUCT(--([Brand1.xls]Finished!$L$1:$L$1000=A3))>0,"X","")

=sumproduct() won't work with whole columns. So adjust that range to be large
enough not to worry about.

I'm having trouble getting cell formulas to update when they reference
a saved workbook.
The situation I have has one workbook for each of 3 brands of hardwood
floors, and a fourth workbook listing trim and molding pieces to go
with those floors. The structure of the workbooks looks something like;

G:\
...Sales Admin\
|
|-BRAND 1\
| |-Brand1.xls
|
|-BRAND 2\
| |-Brand2.xls
|
|-BRAND 3\
| |-Brand3.xls
|
|-WOOd TRIM\
| |-Trim.xls
|

In each of the Brand spreadsheets there is a listing of floor products,
and a column in those products that lists the product code of the
particular piece of trim that is recomended for that floor. In the Trim
spreadsheet there is a list of trim products with about 1900 entries,
each with a unique product code in the first column. In the Trim
spreadsheet there are three columns that are supposed to check to see
which brand of floors that piece of trim is used with. The column looks
in one of the brand workbooks and checks the column of recomended trim.
If it finds the product code for that row then it puts an 'X'
indicating a match. The point of this is that each piece of trim isn't
brand specific, and can apear on one, multiple, or no floor worksheets.
Our users want to be able to sort and filter the trim table by these
brand columns to only get the products associated with the floors they
use.

The formula to preform this lookup looks like this;

=IF(COUNTIF('G:\...Sales Admin\BRAND
1\[Brand1.xls]Finished'!$L:$L,A3)>0,"X","")

'Finished' is the sheet name these floors are on, '$L' is the column in
that spreadsheet where the recomended trim is, and A3 is the cell for
this row that has the product code. If the product code appears one or
more times in column L of the floor table, count will be greater than
zero and the statement will print an X, ortherwise the cell will be
blank. There is a similar function for each of the three columns, and
this function fills down through all of the rows of the trim table.

The problem I'm having is that the function will only update if the
Brand spreadsheet is open. When I try to update the table with the
brand spreadsheets closed I get a #VALUE! 'A value used in the formula
is of the wrong data type' error for all of the lookups. Using the
Evaluate Formula tool the error seems to come from the CountIf call.
After that step #VALUE! is returned and used for the rest of the
evaluation. If I open up one of the Brand spreadsheets then the cells
in that column will automatically update themselves and replace the
#VALUE! error. The only way I can get the columns to update is to open
all three spreadsheets. I haven't had this kind of problem in the past
when trying to create links from one spreadsheet to another. The
problem might be just with the size of the tables. Originally this
update took 8 minutes to complete, and now with a more effective search
is down to 1 minute. I need to get this table to update automatically
without having to open all of the tables it pulls from. I haven't been
able to find a solution to this and would appreciate any assitance.

Paul Zipko