List Visible Data
 

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

List Visible Data
 

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

List Visible Data
 

The array formula you posted worked just fine!!

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


"Ron Coderre" wrote:

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

List Visible Data
 

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

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

Regards,

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

"Jakobshavn Isbrae" wrote in
message ...
The array formula you posted worked just fine!!

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


"Ron Coderre" wrote:

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