R
rjreilly
I import data to spreadsheets from Access via MS query. The range of data
retrurns to A20:X900. In rows 1 - 20 I have sume summary statistics
referencing the underlying Data (A20:X1500). I also apply logic in columns Y
and Z which are not part of the range for query result.
Summary statistics in rows 1 to 20 are based on sumproducts which reference
combibnations of arrays across columns A through Z
The issue: Even though I use hard references (ex. $a$21:$A$2000) in my
summary statistic formulas, when the MS query is refreshed Excel modifies
references in summary statistic formulas despite my hard reference. Only the
columnar references that are within the query range are affected (logical
refrences to column Y and Z are not).
for example; on refresh of the query
SUMPRODUCT(--($Z$22:$Z$2000<>""),--($Z$22:$Z$2000>0))
will mysteriously become
SUMPRODUCT(--($Z$22:$Z$1873<>""),--($Z$22:$Z$2000>0))
(My range references are intended to be large to accomidate an increase in
query results)
Any help would be greatly appreciated.
retrurns to A20:X900. In rows 1 - 20 I have sume summary statistics
referencing the underlying Data (A20:X1500). I also apply logic in columns Y
and Z which are not part of the range for query result.
Summary statistics in rows 1 to 20 are based on sumproducts which reference
combibnations of arrays across columns A through Z
The issue: Even though I use hard references (ex. $a$21:$A$2000) in my
summary statistic formulas, when the MS query is refreshed Excel modifies
references in summary statistic formulas despite my hard reference. Only the
columnar references that are within the query range are affected (logical
refrences to column Y and Z are not).
for example; on refresh of the query
SUMPRODUCT(--($Z$22:$Z$2000<>""),--($Z$22:$Z$2000>0))
will mysteriously become
SUMPRODUCT(--($Z$22:$Z$1873<>""),--($Z$22:$Z$2000>0))
(My range references are intended to be large to accomidate an increase in
query results)
Any help would be greatly appreciated.