FILTER ONE MULTIPLE SHEETS
 

Hello people,
here is my problem:

Suppose i have two worksheets (FIRST, SECOND) with 5 columns each (col. A is
for COLOR1 and col. B is for COLOR2)
FIRST worksheet will have about 1000 records. In SECOND worksheet I want to
have only records which has either in col. A or col. B (for example) the word
'RED'
Is that possible for EXCEL? And if I enter a new record in FIRST worksheet
which has the word RED will it be added to SECOND worksheet?

I hope my problem is clear, if not I will repeat it, thanks everyone

Here's one play to try out ..

Assume the sample data below is
in sheet: First, cols A to E,
data from row2 down

Color1 Color2 Rec1 Rec2 Rec3
Yellow Pink Data1 Data11 Data111
Red Green Data2 Data12 Data112
Orange Red Data3 Data13 Data113
Black White Data4 Data14 Data114
Red Red Data5 Data15 Data115
Black White Data6 Data16 Data116
Black Blue Data7 Data17 Data117
etc

In an empty col to the right, say col G?

Put in G2:
=IF(OR(TRIM(A2)="Red",TRIM(B2)="Red"),ROW(),"")

Copy G2 down to say G2000, to cover the max expected data range in cols A to
E (can copy down ahead of expected data input)

In sheet: Second
-----------------
Paste the same headers from First into A1:E1, viz.:
Color1 Color2 Rec1 Rec2 Rec3

Put in A2:

=IF(ISERROR(SMALL(First!$G:$G,ROWS($A$1:A1))),"",I NDEX(First!A:A,MATCH(SMALL
(First!$G:$G,ROWS($A$1:A1)),First!$G:$G,0)))

Copy A2 across to E2, fill down to E2000
(cover the same range as in sheet: First)

For the sample data above, you'll get:

Color1 Color2 Rec1 Rec2 Rec3
Red Green Data2 Data12 Data112
Orange Red Data3 Data13 Data113
Red Red Data5 Data15 Data115
(rest are blank [""] rows)

And the table in Second will update automatically depending on what's
entered for the colors in First's cols A and/or B
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"ANDRIP" wrote in message
...
Hello people,
here is my problem:

Suppose i have two worksheets (FIRST, SECOND) with 5 columns each (col. A
is
for COLOR1 and col. B is for COLOR2)
FIRST worksheet will have about 1000 records. In SECOND worksheet I want
to
have only records which has either in col. A or col. B (for example) the
word
'RED'
Is that possible for EXCEL? And if I enter a new record in FIRST worksheet
which has the word RED will it be added to SECOND worksheet?

I hope my problem is clear, if not I will repeat it, thanks everyone