Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT vs Database Query
Good Morning,
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT vs Database Query
I'm not sure that your original "From" sumproduct indicated works in the
first place? Don't think sumproduct supports the evaluation of the uneven arrays that it contains. Btw, I've responded since in your other thread, http://tinyurl.com/kowvsk The play should return the required results. Appreciate it if you could close off the discussions there. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Vanessa Simmonds" wrote: Good Morning, 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT vs Database Query
Good Morning, Max.
Trust me, it does work. But my only problem is when I refresh my query. Do you have any suggestion? Thank you very much, Vanessa "Max" wrote: I'm not sure that your original "From" sumproduct indicated works in the first place? Don't think sumproduct supports the evaluation of the uneven arrays that it contains. Btw, I've responded since in your other thread, http://tinyurl.com/kowvsk The play should return the required results. Appreciate it if you could close off the discussions there. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Vanessa Simmonds" wrote: Good Morning, 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT vs Database Query
Do you have any suggestion?
Nein, I'm out of further comments here. You might want to re-surface it to others' radar via a new thread. Poor me still struggling to understand how your original sumproduct expression works as-is. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
database query not showing foxpro database How I import data | New Users to Excel | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
MS Query not installed for New Database Query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |