|
SUMPRODUCT vs Database Query
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 |
SUMPRODUCT vs Database Query
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 "Vanessa Simmonds" wrote: 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 |
| All times are GMT +1. The time now is 02:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com