#VALUE! error in master when linked sheets aren't open?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I've run into a bit of a weird problem in a master workbook I'm
creating. In essence, it's a roll-up of about 20 different
multi-departmental metrics, and links to nearly as many sources. Out of all
of these sources, two of them are CSV files converted to Excel format (and
saved as such). When the book autoupdates on opening, it is able to update
all sheets except for those updating from the CSV-converted-XLS
sources--unless the actual sources are opened at that time, I get a table
full of #VALUE! errors... and when I open the related files, it goes back to
the proper values. A key problem lies in the fact that the two sources which
return the bad values are for just one week of data. Each week, there's a
new source for both. So by end of year, I'd be looking at nearly 100 books
I'd have to open in order to properly update... obviously not an option. Has
anyone ever experienced this? I've linked to dozens of books in the past and
never incurred this problem; I suspect it's because these are converted from
..CSV files but I have no idea how to go about fixing it. Any ideas?

Appreciate any help anyone can provide.

Thanks

Jamie
 
Note--it appears the problem actually is originating with the SUMIF I'm
using while referring to the cell... here's the actual value of the master
cell:

=SUMIF('C:\Documents and Settings\jwatt\My Documents\Spreadsheets\RMA Weekly
Data\[RMA_Aging072807.xls]RMA_Aging072807'!C:C, "Entered", 'C:\Documents and
Settings\jwatt\My Documents\Spreadsheets\RMA Weekly
Data\[RMA_Aging072807.xls]RMA_Aging072807'!D:D)

Is SumIf only able to perform a function on a given range if the related
sheet is open?
 
There are some worksheet functions that won't work with closed workbooks--but
IIRC, most of them would return #ref! errors.

=sumif(), =countif(), =indirect()

You could use an array formula:
=SUM(IF('C:\Documents and Settings\jwatt\My Documents\Spreadsheets\RMA Weekly
Data\[RMA_Aging072807.xls]RMA_Aging072807'!C1:C99, "Entered", 'C:\Documents and
Settings\jwatt\My Documents\Spreadsheets\RMA Weekly
Data\[RMA_Aging072807.xls]RMA_Aging072807'!D1:D99))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

You could use =sumproduct(), too. This doesn't need to be array entered, but it
suffers from the same range limitations as the array formula:

=SUMproduct(--('C:\Documents and Settings\jwatt\My Documents\Spreadsheets\RMA
Weekly
Data\[RMA_Aging072807.xls]RMA_Aging072807'!C1:C99="Entered"), 'C:\Documents and
Settings\jwatt\My Documents\Spreadsheets\RMA Weekly
Data\[RMA_Aging072807.xls]RMA_Aging072807'!D1:D99)

===
In either case, I find it much easier creating the formula when the "sending"
workbook is open. Then when I close that workbook, excel will adjust the
formula to include the path and filename.
Note--it appears the problem actually is originating with the SUMIF I'm
using while referring to the cell... here's the actual value of the master
cell:

=SUMIF('C:\Documents and Settings\jwatt\My Documents\Spreadsheets\RMA Weekly
Data\[RMA_Aging072807.xls]RMA_Aging072807'!C:C, "Entered", 'C:\Documents and
Settings\jwatt\My Documents\Spreadsheets\RMA Weekly
Data\[RMA_Aging072807.xls]RMA_Aging072807'!D:D)

Is SumIf only able to perform a function on a given range if the related
sheet is open?

MJW said:
Hi All,

I've run into a bit of a weird problem in a master workbook I'm
creating. In essence, it's a roll-up of about 20 different
multi-departmental metrics, and links to nearly as many sources. Out of all
of these sources, two of them are CSV files converted to Excel format (and
saved as such). When the book autoupdates on opening, it is able to update
all sheets except for those updating from the CSV-converted-XLS
sources--unless the actual sources are opened at that time, I get a table
full of #VALUE! errors... and when I open the related files, it goes back to
the proper values. A key problem lies in the fact that the two sources which
return the bad values are for just one week of data. Each week, there's a
new source for both. So by end of year, I'd be looking at nearly 100 books
I'd have to open in order to properly update... obviously not an option. Has
anyone ever experienced this? I've linked to dozens of books in the past and
never incurred this problem; I suspect it's because these are converted from
.CSV files but I have no idea how to go about fixing it. Any ideas?

Appreciate any help anyone can provide.

Thanks

Jamie
Click to expand...
Click to expand...
 
Back
Top