Cell/Range references and Microsift Query

[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.

 

[FSt1]

hi
not sure..guessing here but....
right click the query range. from the popkup click data range properties
at the bottom of the property box, uncheck fill down fomulas in adjacent
columns.
this may seem odd but you can put formulas at the bottom of the query range
to sum columns. with the fill down box check, excel will adjust the formula
to accomidate varing length queries as well as fill down (and up) formulas.
i am quessing that excel is trying to adjust the formulas at the top of the
query range this time. i have never had formulas at the top before so this is
a quess.

regards
FSt1

 

[rjreilly]

Much appreciated; will try.

Thanking you.

hi
not sure..guessing here but....
right click the query range. from the popkup click data range properties
at the bottom of the property box, uncheck fill down fomulas in adjacent
columns.
this may seem odd but you can put formulas at the bottom of the query range
to sum columns. with the fill down box check, excel will adjust the formula
to accomidate varing length queries as well as fill down (and up) formulas.
i am quessing that excel is trying to adjust the formulas at the top of the
query range this time. i have never had formulas at the top before so this is
a quess.

regards
FSt1