SUMPRODUCT vs Database Query

[Vanessa Simmonds]

Good Afternoon,

I am using SUMPRODUCT to return the sum of a selection from a spreadsheet
that it downloaded from an access database.

However everytime I refresh my database query, my SUMPRODUCT formula changes:

FROM: (SUMPRODUCT(('SU Shipments'!$E$1:$AA$1='Daily Shipments'!C$7)*('SU
Shipments'!$D$2:$D$1000='Daily Shipments'!$B9)*('SU
Shipments'!$E$2:$AA$1000)))/1000

TO: (SUMPRODUCT(('SU Shipments'!$E$1:$AA$1='Daily Shipments'!C$7)*('SU
Shipments'!$D$2:$D$1005='Daily Shipments'!$B9)*('SU
Shipments'!$E$2:$AA$1000)))/1000

As a result instead of getting values on my summary page I get "N/A".

Is there a way to prevent the formulas from changing everytime I refresh my
database?

Best Regards,

Vanessa Simmonds

 

[Sean Timmons]

May I suggest INDIRECT?

(SUMPRODUCT((INDIRECT("'SU Shipments'!$E$1:$AA$1")='Daily
Shipments'!C$7)*(INDIRECT("'SU Shipments'!$D$2:$D$1000")='Daily
Shipments'!$B9)*(INDIRECT("'SU Shipments'!$E$2:$AA$1000"))))/1000