Array formula changing

[Michael]

This is an array formula I use

{=SUM(('iAplication Data'!$C$2:$C$25000="Three")*
('iAplication Data'!$FS$2:$FS$25000=B42)*('iAplication
Data'!$Y$2:$Y$25000>0))}

When I update the data in the the iApplication Data sheet
(data is from a query) and then recalc the whole workbook
the references in the formula change to:-

{=SUM(('iAplication Data'!$C$2:$C$25095="Three")*
('iAplication Data'!$FS$2:$FS$25000=B42)*('iAplication
Data'!$Y$2:$Y$25095>0))}

and returns #N/A in the cell because the ranges do not
match.

Is there a way to keep the ranges consistent. Have
noticed this before in some other Array formulas I use.

I set the range to 25000 as the Sheet is updated daily
and more data added to it so 25000 covers any data that
may be added over a whole month.

Also the data in FS is data that has been manually
entered so is not affected by the query so this may be
why it does not change but the changes in the other
references are the problem.

Hope someone can understand this and shed some light on
how to resolve it.

 

[Leo Heuser]

Michael

You can use the INDIRECT-function, like this:

{=SUM((INDIRECT("'iAplication Data'!$C$2:$C$25000")="Three")*
('iAplication Data'!$FS$2:$FS$25000=B42)*(INDIRECT("'iAplication
Data'!$Y$2:$Y$25000")>0))}

In this situation it's OK to use

=SUMPRODUCT((INDIRECT("'iAplication Data'!$C$2:$C$25000")="Three")*
('iAplication Data'!$FS$2:$FS$25000=B42)*(INDIRECT("'iAplication
Data'!$Y$2:$Y$25000")>0))

and then enter the formula with just <Enter>

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

 

[Harlan Grove]

...
...

In this situation it's OK to use

=SUMPRODUCT((INDIRECT("'iAplication Data'!$C$2:$C$25000")="Three")*
('iAplication Data'!$FS$2:$FS$25000=B42)*(INDIRECT("'iAplication
Data'!$Y$2:$Y$25000")>0))

and then enter the formula with just <Enter>

...

This does ensure that the ranges never change but seems like using asprin to
treat headaches due to brain tumors. I think there's a much more serious
underlying problem that needs to be addressed.
...

Making it appear that corresponding data is added in columns C and Y but not
column FS. If this is the case, why would you believe that there's any
correspondence of values in columns C and Y on the one hand and column FS on the
other in any of the rows 2 through 25000. Are data in columns C and Y always
added to the bottom of the range? If so, why bother adding data below row 25000
if it wouldn't be used in your formula?

Repetition: why do you believe values in each row in column FS still correspond
to values in columns C and Y? Is there some column nearer to column FS that
could programatically establish a correspondence to the data in columns C and Y
so that all the updated data in columns C and Y could be used?

It's possible to define consistent dynamic named ranges that could be used in
your conditional sum formula to sum over all updated data. However, I doubt that
fixing the formula without fixing column FS would solve the problem. It'd give
you numeric results rather than error values, but it's not immediately obvious
that those numeric values would be anything other than garbage.

 

[Michael]

Leo,

Thanks for the advice I'll give it a go.

Harlan,

FS is a coversion of a date/time stamp at row a and is
the only way I can think of to get the day by itself (its
INT(A2) or whatever) and soes have a relationshit with C
and Y . Any new data is added to the bottom. Maybe I can
get around this easier but it appears to work ok except
for the glitch.

 

[Harlan Grove]

...
...

FS is a coversion of a date/time stamp at row a and is
the only way I can think of to get the day by itself (its
INT(A2) or whatever) and soes have a relationshit with C
and Y . Any new data is added to the bottom. Maybe I can
get around this easier but it appears to work ok except
for the glitch.

The glitch could only be 'resolved' by excluding newly added rows of data. Are
conditional sums excluding rows below some arbitrary cut-off acceptable? If not,
the 'glitch' is a fatal flaw that can only be resolved by extending column FS as
well or eliminating references to column FS and working directly with updated
data that would have fed FS.

If column A is updated along with C and Y, then whatever formula you use to
generate FS based on A could be built directly into the formula as a function of
A rather than FS. If you really mean FS2 should contain =INT(A2), then

=SUMPRODUCT(('iAplication Data'!$C$2:$C$25095="Three")*
(INT('iAplication Data'!$A$2:$A$25095)=B42)*
('iAplication Data'!$Y$2:$Y$25095>0))

should work. If the function (in the theoretical sense) mapping column A to
column FS is more complicated, it's may still be possible to use column A rather
than column FS.

 

[Leo Heuser]

You're welcome, Michael.

LeoH