Problems when sorting data containing array formulas
 

Hi all

After sorting a table of data based on a field, in this instance €œName€� the
array formulas which compare values within said rows against fixed values
using ={SUM(IF€¦ are still looking up values within the newly sorted row
location as if they were $d, but outputting the answer in the pre-sorted
row. I have not $d them to allow filling down of the formulas.

Confused?

E.g. Row 2 is named €œzebra€� and is currently sorted by €œquantity€� of Zebras
(lowest to highest) with an array formula in row 2, looking up row 2
conditions against a $d range of values, outputting a solution in row 2.

I now sort by alphabetically by €œName€� placing €œZebra€� at the bottom in row
654 yet in the array formula of Row 2, it is now looking at row 654 and
outputting the solution in row 2!?

Basically it is outputting the correct solution for €œZebra€� conditions but
placing it with the wrong field, Row 2, ie for Aardvark.

This is not happening for all other non-array formulas I am using, and this
seems to go against Excels functionality for allowing data sorts with
formulas. Is there something Im missing or am overlooking in my use of array
formulas?

Appreciate your help.

--
Thanks for your time!