Hi
see your other post

--
Regards
Frank Kabel
Frankfurt, Germany


Carpie wrote:
I have approximately 10 spreadsheets in which people from multiple
locations enter in savings data each month. There are two columns of
particular interest in these spreadsheets. One is YTD Savings which
sums the savings of all the month's entries up until the current
month. The other column is Year End Forecast which sums all of the
months through December. Everything works fine in these
spreadsheets.

I have an eleventh spreadsheet which pulls data from those 10
spreadsheets for a summary. This eleventh spreadsheet had 10 tabs
(one for each spreadsheet) and sums up the totals in the YTD Savings
and YE Forecast columns depending on what is in another column
(Enabler). To accomplish this, I use the SUMPRODUCT equation.

An example of the equations are (removing long file paths):
(SUMPRODUCT(--('[LeHavre.xls]LeHavre'!$E$1:$E$20000="Direct
Material"),'[LeHavre.xls]LeHavre'!$J$1:$J$20000))/1000000

AND

(SUMPRODUCT(--('[LeHavre.xls]LeHavre'!$E$1:$E$20000="Direct
Material"),'[LeHavre.xls]LeHavre'!$L$1:$L$20000))/1000000

These sum up the YTD Actual and YE Forecast savings if the Enabler
equals "Direct Material". Remember that I use the exact same
equation in nine other spreadsheets with nothing different than
referencing a different source file.

OK, so now the problem. Everything has worked fine with these
spreadsheets for the last 5-6 months. Now this month, I get a "#N/A"
result in two of my spreadsheets for the YTD Actual summation. The
YE Forecast for each of these spreadsheets still works fine!

I'm at a complete loss here. At first I thought it might be poor
data entry but remember that the YTD Actual and YE Forecast values
are not entered data, but equations that sum up entered data. So
there is very little chance they have been tweaked.

Any thoughts?