Cell address if value matches some criteria

[asamani]

Hi,

Is there a way to get excel to display a list of addresses for cells that match a particular criteria? For example, I have c1000 values ranging from +5.0 to -5.0. i want to list the cell address of values below -2. i can use conditional formatting to highlight these values, but I need to be able to extract them to a different sheet or a separate area of the same sheet.

Thanking you in anticipation,
asamani.

[StephenR]

One way would be to add a helper column with a formula like IF (A1<-2,1,"") and then use Advanced Filter to select all the ones with a 1.

Or, if your data are in A1:A12 (with a header cell) in say C2 enter =A2<-2, Advanced Filter > list range A1:A12 > criteria range C1:C2 > copy to another location.