List Visible Data

[Jakobshavn Isbrae]

In Sheet1 in column A I have filtered data. Some of the data is visible,
some is hidden.

In Sheet2 in column A I would like to list the data from Sheet1 that is
currently visible. If I change the filter settings on Sheet1, I need the
data displayed on Sheet2 to reflect the change automatically.

I can do this with some Visual Basic, but I cannot use the VB because it has
to function on computers with a variety of security settings.

I suspect that this is a FAQ type question, but I have not run across the
answer.

 

[Ron Coderre]

With
Sheet1, A1:A30 containing a list of filtered items, with A1 as the heading.

This is messy....but, here's what I came up with:
On Sheet2:
These formulas display the visible items from that list.

Enter this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER,
instead of just ENTER)in cell:
A2: =IF(SUBTOTAL(3,Sheet1!$A$2:$A$30)<ROWS($2:2),"",
INDEX(Sheet1!$A$1:$A$30,SMALL(SUBTOTAL(3,OFFSET(Sheet1!$A$1,ROW($A$1:$A$30),0,1))*
ROW($A$1:$A$30)+1,ROWS($2:2)+ROWS(Sheet1!$A$1:$A$30)-SUBTOTAL(3,Sheet1!$A$2:$A$30))))

Copy A2 into A3 and down as far as you need.

OR....this NON-array formula (committed with just ENTER):
A2:
=IF(SUBTOTAL(3,Sheet1!$A$2:$A$30)<ROWS($2:2),"",INDEX(Sheet1!$A$1:$A$30,INDEX(
SMALL(INDEX(SUBTOTAL(3,OFFSET(Sheet1!$A$1,ROW($A$1:$A$30),0,1))*ROW($A$1:$A$30)+1,0),
ROWS($2:2)+ROWS(Sheet1!$A$1:$A$30)-SUBTOTAL(3,Sheet1!$A$2:$A$30)),0)))

Copy A2 down as far as you need.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Jakobshavn Isbrae]

The array formula you posted worked just fine!!

Thank you very much for taking the time to help me.

 

[Ron Coderre]

You're very welcome....I'm glad I could help.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)