Greetings all!
I am working on a dynamic reference for our accountant to allow him to get cost data on different items being produced. I have a database that has production data entered for each day and I am trying to pull the data for various time frames based on a =NOW() value in A1.
I am using the SUMPRODUCT function as the file with the data may or may not be open when the lookup is performed, and this is the formula I am having some difficulty with:
I don't have any problem with the FG_Number, but the FillDate seems to be throwing a #VALUE error. I don't know how to track that down to fix any bad data. The data all looks correct, but either I wrote the formula wrong or there is bad data hiding in the range.
I would appreciate any help.
Jake
I am working on a dynamic reference for our accountant to allow him to get cost data on different items being produced. I have a database that has production data entered for each day and I am trying to pull the data for various time frames based on a =NOW() value in A1.
I am using the SUMPRODUCT function as the file with the data may or may not be open when the lookup is performed, and this is the formula I am having some difficulty with:
Code:
=SUMPRODUCT(--('Production Analysis v2.00.xlsm'!FG_Number=D5)*--(MONTH('Production Analysis v2.00.xlsm'!FillDate)=(MONTH(A1)))*--('Production Analysis v2.00.xlsm'!Completed)))
I don't have any problem with the FG_Number, but the FillDate seems to be throwing a #VALUE error. I don't know how to track that down to fix any bad data. The data all looks correct, but either I wrote the formula wrong or there is bad data hiding in the range.
I would appreciate any help.
Jake