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(Sh eet1!$A$1,ROW($A$1:$A$30),0,1))*
ROW($A$1:$A$30)+1,ROWS($2:2)+ROWS(Sheet1!$A$1:$A$3 0)-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),"",IN DEX(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" wrote in
message ...
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.
--
jake