M
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.
{=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.