extract items from filtered list

I need to set up a report that dynamically displays on another sheet items
from one column of a filtered range. After the advanced filter is executed,
I may have 12 records left showing out of the 400. On sheet2 I need to
display the visible data from one of the columns, let's say in cells A1:A12.
So this would be one of those "copy down" functions that displays the first
item in A1, the second in A2, etc. I've been tinkering with all the INDEX,
OFFSET, SUBTOTAL combinations, but am not quite getting it.

TIA

Try this array formula**.

Assume:

The full range that you want to extract the data from is A2:A20 and this
range is given the defined name Rng.

Array entered** on some other sheet starting in cell A1:

=IF(ROWS(A$1:A1)=SUBTOTAL(3,Rng),INDEX(Rng,MATCH( ROWS(A$1:A1),SUBTOTAL(3,OFFSET(Rng,,,ROW(Rng)-MIN(ROW(Rng))+1)),0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down until you get blanks

--
Biff
Microsoft Excel MVP


"andy62" wrote in message
...
I need to set up a report that dynamically displays on another sheet items
from one column of a filtered range. After the advanced filter is
executed,
I may have 12 records left showing out of the 400. On sheet2 I need to
display the visible data from one of the columns, let's say in cells
A1:A12.
So this would be one of those "copy down" functions that displays the
first
item in A1, the second in A2, etc. I've been tinkering with all the
INDEX,
OFFSET, SUBTOTAL combinations, but am not quite getting it.

TIA