Filtering data to another sheet

[lsy]

In excel there is a fucntion that can filter particular data in the current page... but can i filter and display in another page!!

example sheet1:
Col1 Col2 Col3 Col4
abc def ghi jkl
bbb ddd ggg jjj
bac fed ghi klj

eg sheet2:
ColX ColY
ghi jkl
ghi klj

so if i have a new data enter in Col3 as ghi in sheet1 then sheet2 will auto have that new record in ColX n ColY!! that is something like linking 2 sheet..

[Max]

Perhaps something along these lines ..

In Sheet1, data is assumed in cols A to D, from row1 down
with the key column being col C

Put in say, F1: =IF(TRIM(C1)="","",IF(TRIM(C1)=Sheet2!$X$1,ROW(),""))
Copy F1 down to say F100, to cover the max expected data in col C

In Sheet2
------------
Let's reserve X1 for input of the item of interest
Input in X1: ghi

Put in X2:
=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!C:C,MATCH(SMA
LL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))

Copy X2 across to Y2, fill down to Y101
(cover the same range as in col F in Sheet1)

For the input in X1,
you'll get the desired filter results in cols X and Y, from row2 down

And the results in Sheet2 will auto-update for any subsequent changes (e.g.:
new data input) made within Sheet1's col C
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"lsy" <[email protected]> wrote in message
news:[email protected]...
>
> In excel there is a fucntion that can filter particular data in the
> current page... but can i filter and display in another page!!
>
> example sheet1:
> Col1 Col2 Col3 Col4
> abc def ghi jkl
> bbb ddd ggg jjj
> bac fed ghi klj
>
> eg sheet2:
> ColX ColY
> ghi jkl
> ghi klj
>
> so if i have a new data enter in Col3 as ghi in sheet1 then sheet2 will
> auto have that new record in ColX n ColY!! that is something like
> linking 2 sheet..
>
>
> --
> lsy
> ------------------------------------------------------------------------
> lsy's Profile:
http://www.excelforum.com/member.php...o&userid=23917
> View this thread: http://www.excelforum.com/showthread...hreadid=375546
>

[KL]

Hi lsy,

You can also explore the functionality of the Advanced Filter (menu
Data>Filter>Advanced Filter). You may need to create a criteria range which
should be vertical and include the column header (exactly as in the original
table) and at least one row for filtering criteria for each column you want
filter by. For example:

if your database has the following columns:
ID, Name, Sales, Month

then if you create the following criteria range on the output sheet:
[A1]="Sales"
[A2]=>100
[B1]="Month"
[B2]="Jan"

the Advanced Filter output will show all sales higher than 100 in January.

In order to use Advanced Filter on a sheet other than the database, first
activate the destination sheet and then go to menu Data>Filter>Advanced
Filter. Chose the "copy to another location" option. Enter the references to
the ranges of the database "List Range" (say Sheet1!A1:D1000), "Criteria
Range" (say A1:B2 in our example) and "Copy To" (say A3 - two rows below the
criteria range) and press OK.

Regards,
KL


"lsy" <[email protected]> wrote in message
news:[email protected]...
>
> In excel there is a fucntion that can filter particular data in the
> current page... but can i filter and display in another page!!
>
> example sheet1:
> Col1 Col2 Col3 Col4
> abc def ghi jkl
> bbb ddd ggg jjj
> bac fed ghi klj
>
> eg sheet2:
> ColX ColY
> ghi jkl
> ghi klj
>
> so if i have a new data enter in Col3 as ghi in sheet1 then sheet2 will
> auto have that new record in ColX n ColY!! that is something like
> linking 2 sheet..
>
>
> --
> lsy
> ------------------------------------------------------------------------
> lsy's Profile:
> http://www.excelforum.com/member.php...o&userid=23917
> View this thread: http://www.excelforum.com/showthread...hreadid=375546
>

[Max]

Perhaps better to TRIM the input from Sheet2's X1 as well,
so put instead in F1 in Sheet1, and copy down:
=IF(TRIM(C1)="","",IF(TRIM(C1)=TRIM(Sheet2!$X$1),ROW(),""))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[lsy]

i still not very clear with the solution... what i want is anything key in in the col3 with the data with ghi in sheet 1 will auto reflect a record in sheet2... is that possible??

[Max]

"lsy" wrote:
> i still not very clear with the solution... what i want is anything key
> in in the col3 with the data with ghi in sheet 1 will auto reflect a
> record in sheet2... is that possible??

That's exactly what the suggested construct does !

Here's a sample file with the construct implemented:
http://flypicture.com/p.cfm?id=56960

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: lsy_wksht_1.xls

Play with the file. Try adding further to the sample data in Sheet1. The
results will be updated in Sheet2 automatically.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[lsy]

yes.. this is exact what i want!! thanks...
can u please explain to me what <=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!C:C,MATCH(SMALL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))> means??? so that i can reuse it!!

thanks..

"lsy" wrote:
> i still not very clear with the solution... what i want is anything key
> in in the col3 with the data with ghi in sheet 1 will auto reflect a
> record in sheet2... is that possible??

That's exactly what the suggested construct does !

Here's a sample file with the construct implemented:
http://flypicture.com/p.cfm?id=56960

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: lsy_wksht_1.xls

Play with the file. Try adding further to the sample data in Sheet1. The
results will be updated in Sheet2 automatically.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"lsy" wrote
> yes.. this is exact what i want!! thanks...

Glad to hear that ..

> can u please explain to me what
>
<=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!C:C,MATCH(SM
ALL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))
>> means??? so that i can reuse it!!

The construct functions along these lines ..

In Sheet1:

Col F is a helper col where we frame up the "filter" criteria to pick out
values in the key col C which will match with what is input in Sheet2's A1.

Matched values in col C will return arbitrary row numbers in col F. These
arb row numbers in col F will in turn be read by the extract formulas in
Sheet2

TRIM is used to improve robustness in the matching. It'll remove extraneous
space(s) present - if any- which might otherwise throw valid matching cases
off.

In Sheet2:

The core formula placed in X2 is the
INDEX(Sheet1!C:C,MATCH(SMALL(...),...)).

SMALL(...) points to the criteria col F in Sheet1, and returns an ascending
sort of the arbitrary row numbers in col F, with any blanks thrown below.

MATCH(SMALL(...),...)) then returns the positions of the values returned by
SMALL(...) matched against the lookup_array, which again is Sheet1's col F.

INDEX(Sheet1!C:C, ..) then simply retrieves the corresponding values from
Sheet1's col C. INDEX(Sheet1!C:C, ..) in X2 copied across to Y2 will result
in INDEX(Sheet1!D:D, ..), i.e. increment the col C to D, hence returning
corresponding values from col D in Sheet1 into col Y.

Remember to fix the references to Sheet1's col F in the formula, i.e. use
Sheet1!$F:$F (with the dollar signs).

The error trap " IF(ISERROR(SMALL(...),"",INDEX(...)) " is used to return
blanks: "" (instead of #NUM!s) for a cleaner "blank" look once the returns
for all the arb row numbers from Sheet1's col F have been exhausted in
Sheet2

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

Perhaps better to TRIM the input from Sheet2's X1 as well,
so put instead in F1 in Sheet1, and copy down:
=IF(TRIM(C1)="","",IF(TRIM(C1)=TRIM(Sheet2!$X$1),ROW(),""))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"lsy" wrote
> yes.. this is exact what i want!! thanks...

Glad to hear that ..

> can u please explain to me what
>
<=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!C:C,MATCH(SM
ALL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))
>> means??? so that i can reuse it!!

The construct functions along these lines ..

In Sheet1:

Col F is a helper col where we frame up the "filter" criteria to pick out
values in the key col C which will match with what is input in Sheet2's A1.

Matched values in col C will return arbitrary row numbers in col F. These
arb row numbers in col F will in turn be read by the extract formulas in
Sheet2

TRIM is used to improve robustness in the matching. It'll remove extraneous
space(s) present - if any- which might otherwise throw valid matching cases
off.

In Sheet2:

The core formula placed in X2 is the
INDEX(Sheet1!C:C,MATCH(SMALL(...),...)).

SMALL(...) points to the criteria col F in Sheet1, and returns an ascending
sort of the arbitrary row numbers in col F, with any blanks thrown below.

MATCH(SMALL(...),...)) then returns the positions of the values returned by
SMALL(...) matched against the lookup_array, which again is Sheet1's col F.

INDEX(Sheet1!C:C, ..) then simply retrieves the corresponding values from
Sheet1's col C. INDEX(Sheet1!C:C, ..) in X2 copied across to Y2 will result
in INDEX(Sheet1!D:D, ..), i.e. increment the col C to D, hence returning
corresponding values from col D in Sheet1 into col Y.

Remember to fix the references to Sheet1's col F in the formula, i.e. use
Sheet1!$F:$F (with the dollar signs).

The error trap " IF(ISERROR(SMALL(...),"",INDEX(...)) " is used to return
blanks: "" (instead of #NUM!s) for a cleaner "blank" look once the returns
for all the arb row numbers from Sheet1's col F have been exhausted in
Sheet2

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"lsy" wrote:
> i still not very clear with the solution... what i want is anything key
> in in the col3 with the data with ghi in sheet 1 will auto reflect a
> record in sheet2... is that possible??

That's exactly what the suggested construct does !

Here's a sample file with the construct implemented:
http://flypicture.com/p.cfm?id=56960

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: lsy_wksht_1.xls

Play with the file. Try adding further to the sample data in Sheet1. The
results will be updated in Sheet2 automatically.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[KL]

Hi lsy,

You can also explore the functionality of the Advanced Filter (menu
Data>Filter>Advanced Filter). You may need to create a criteria range which
should be vertical and include the column header (exactly as in the original
table) and at least one row for filtering criteria for each column you want
filter by. For example:

if your database has the following columns:
ID, Name, Sales, Month

then if you create the following criteria range on the output sheet:
[A1]="Sales"
[A2]=>100
[B1]="Month"
[B2]="Jan"

the Advanced Filter output will show all sales higher than 100 in January.

In order to use Advanced Filter on a sheet other than the database, first
activate the destination sheet and then go to menu Data>Filter>Advanced
Filter. Chose the "copy to another location" option. Enter the references to
the ranges of the database "List Range" (say Sheet1!A1:D1000), "Criteria
Range" (say A1:B2 in our example) and "Copy To" (say A3 - two rows below the
criteria range) and press OK.

Regards,
KL


"lsy" <[email protected]> wrote in message
news:[email protected]...
>
> In excel there is a fucntion that can filter particular data in the
> current page... but can i filter and display in another page!!
>
> example sheet1:
> Col1 Col2 Col3 Col4
> abc def ghi jkl
> bbb ddd ggg jjj
> bac fed ghi klj
>
> eg sheet2:
> ColX ColY
> ghi jkl
> ghi klj
>
> so if i have a new data enter in Col3 as ghi in sheet1 then sheet2 will
> auto have that new record in ColX n ColY!! that is something like
> linking 2 sheet..
>
>
> --
> lsy
> ------------------------------------------------------------------------
> lsy's Profile:
> http://www.excelforum.com/member.php...o&userid=23917
> View this thread: http://www.excelforum.com/showthread...hreadid=375546
>

[Max]

Perhaps something along these lines ..

In Sheet1, data is assumed in cols A to D, from row1 down
with the key column being col C

Put in say, F1: =IF(TRIM(C1)="","",IF(TRIM(C1)=Sheet2!$X$1,ROW(),""))
Copy F1 down to say F100, to cover the max expected data in col C

In Sheet2
------------
Let's reserve X1 for input of the item of interest
Input in X1: ghi

Put in X2:
=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!C:C,MATCH(SMA
LL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))

Copy X2 across to Y2, fill down to Y101
(cover the same range as in col F in Sheet1)

For the input in X1,
you'll get the desired filter results in cols X and Y, from row2 down

And the results in Sheet2 will auto-update for any subsequent changes (e.g.:
new data input) made within Sheet1's col C
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"lsy" <[email protected]> wrote in message
news:[email protected]...
>
> In excel there is a fucntion that can filter particular data in the
> current page... but can i filter and display in another page!!
>
> example sheet1:
> Col1 Col2 Col3 Col4
> abc def ghi jkl
> bbb ddd ggg jjj
> bac fed ghi klj
>
> eg sheet2:
> ColX ColY
> ghi jkl
> ghi klj
>
> so if i have a new data enter in Col3 as ghi in sheet1 then sheet2 will
> auto have that new record in ColX n ColY!! that is something like
> linking 2 sheet..
>
>
> --
> lsy
> ------------------------------------------------------------------------
> lsy's Profile:
http://www.excelforum.com/member.php...o&userid=23917
> View this thread: http://www.excelforum.com/showthread...hreadid=375546
>

[Max]

Perhaps something along these lines ..

In Sheet1, data is assumed in cols A to D, from row1 down
with the key column being col C

Put in say, F1: =IF(TRIM(C1)="","",IF(TRIM(C1)=Sheet2!$X$1,ROW(),""))
Copy F1 down to say F100, to cover the max expected data in col C

In Sheet2
------------
Let's reserve X1 for input of the item of interest
Input in X1: ghi

Put in X2:
=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!C:C,MATCH(SMA
LL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))

Copy X2 across to Y2, fill down to Y101
(cover the same range as in col F in Sheet1)

For the input in X1,
you'll get the desired filter results in cols X and Y, from row2 down

And the results in Sheet2 will auto-update for any subsequent changes (e.g.:
new data input) made within Sheet1's col C
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"lsy" <[email protected]> wrote in message
news:[email protected]...
>
> In excel there is a fucntion that can filter particular data in the
> current page... but can i filter and display in another page!!
>
> example sheet1:
> Col1 Col2 Col3 Col4
> abc def ghi jkl
> bbb ddd ggg jjj
> bac fed ghi klj
>
> eg sheet2:
> ColX ColY
> ghi jkl
> ghi klj
>
> so if i have a new data enter in Col3 as ghi in sheet1 then sheet2 will
> auto have that new record in ColX n ColY!! that is something like
> linking 2 sheet..
>
>
> --
> lsy
> ------------------------------------------------------------------------
> lsy's Profile:
http://www.excelforum.com/member.php...o&userid=23917
> View this thread: http://www.excelforum.com/showthread...hreadid=375546
>

[KL]

Hi lsy,

You can also explore the functionality of the Advanced Filter (menu
Data>Filter>Advanced Filter). You may need to create a criteria range which
should be vertical and include the column header (exactly as in the original
table) and at least one row for filtering criteria for each column you want
filter by. For example:

if your database has the following columns:
ID, Name, Sales, Month

then if you create the following criteria range on the output sheet:
[A1]="Sales"
[A2]=>100
[B1]="Month"
[B2]="Jan"

the Advanced Filter output will show all sales higher than 100 in January.

In order to use Advanced Filter on a sheet other than the database, first
activate the destination sheet and then go to menu Data>Filter>Advanced
Filter. Chose the "copy to another location" option. Enter the references to
the ranges of the database "List Range" (say Sheet1!A1:D1000), "Criteria
Range" (say A1:B2 in our example) and "Copy To" (say A3 - two rows below the
criteria range) and press OK.

Regards,
KL


"lsy" <[email protected]> wrote in message
news:[email protected]...
>
> In excel there is a fucntion that can filter particular data in the
> current page... but can i filter and display in another page!!
>
> example sheet1:
> Col1 Col2 Col3 Col4
> abc def ghi jkl
> bbb ddd ggg jjj
> bac fed ghi klj
>
> eg sheet2:
> ColX ColY
> ghi jkl
> ghi klj
>
> so if i have a new data enter in Col3 as ghi in sheet1 then sheet2 will
> auto have that new record in ColX n ColY!! that is something like
> linking 2 sheet..
>
>
> --
> lsy
> ------------------------------------------------------------------------
> lsy's Profile:
> http://www.excelforum.com/member.php...o&userid=23917
> View this thread: http://www.excelforum.com/showthread...hreadid=375546
>

[Max]

Perhaps better to TRIM the input from Sheet2's X1 as well,
so put instead in F1 in Sheet1, and copy down:
=IF(TRIM(C1)="","",IF(TRIM(C1)=TRIM(Sheet2!$X$1),ROW(),""))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"lsy" wrote:
> i still not very clear with the solution... what i want is anything key
> in in the col3 with the data with ghi in sheet 1 will auto reflect a
> record in sheet2... is that possible??

That's exactly what the suggested construct does !

Here's a sample file with the construct implemented:
http://flypicture.com/p.cfm?id=56960

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: lsy_wksht_1.xls

Play with the file. Try adding further to the sample data in Sheet1. The
results will be updated in Sheet2 automatically.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"lsy" wrote
> yes.. this is exact what i want!! thanks...

Glad to hear that ..

> can u please explain to me what
>
<=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!C:C,MATCH(SM
ALL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))
>> means??? so that i can reuse it!!

The construct functions along these lines ..

In Sheet1:

Col F is a helper col where we frame up the "filter" criteria to pick out
values in the key col C which will match with what is input in Sheet2's A1.

Matched values in col C will return arbitrary row numbers in col F. These
arb row numbers in col F will in turn be read by the extract formulas in
Sheet2

TRIM is used to improve robustness in the matching. It'll remove extraneous
space(s) present - if any- which might otherwise throw valid matching cases
off.

In Sheet2:

The core formula placed in X2 is the
INDEX(Sheet1!C:C,MATCH(SMALL(...),...)).

SMALL(...) points to the criteria col F in Sheet1, and returns an ascending
sort of the arbitrary row numbers in col F, with any blanks thrown below.

MATCH(SMALL(...),...)) then returns the positions of the values returned by
SMALL(...) matched against the lookup_array, which again is Sheet1's col F.

INDEX(Sheet1!C:C, ..) then simply retrieves the corresponding values from
Sheet1's col C. INDEX(Sheet1!C:C, ..) in X2 copied across to Y2 will result
in INDEX(Sheet1!D:D, ..), i.e. increment the col C to D, hence returning
corresponding values from col D in Sheet1 into col Y.

Remember to fix the references to Sheet1's col F in the formula, i.e. use
Sheet1!$F:$F (with the dollar signs).

The error trap " IF(ISERROR(SMALL(...),"",INDEX(...)) " is used to return
blanks: "" (instead of #NUM!s) for a cleaner "blank" look once the returns
for all the arb row numbers from Sheet1's col F have been exhausted in
Sheet2

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[KL]

Hi lsy,

You can also explore the functionality of the Advanced Filter (menu
Data>Filter>Advanced Filter). You may need to create a criteria range which
should be vertical and include the column header (exactly as in the original
table) and at least one row for filtering criteria for each column you want
filter by. For example:

if your database has the following columns:
ID, Name, Sales, Month

then if you create the following criteria range on the output sheet:
[A1]="Sales"
[A2]=>100
[B1]="Month"
[B2]="Jan"

the Advanced Filter output will show all sales higher than 100 in January.

In order to use Advanced Filter on a sheet other than the database, first
activate the destination sheet and then go to menu Data>Filter>Advanced
Filter. Chose the "copy to another location" option. Enter the references to
the ranges of the database "List Range" (say Sheet1!A1:D1000), "Criteria
Range" (say A1:B2 in our example) and "Copy To" (say A3 - two rows below the
criteria range) and press OK.

Regards,
KL


"lsy" <[email protected]> wrote in message
news:[email protected]...
>
> In excel there is a fucntion that can filter particular data in the
> current page... but can i filter and display in another page!!
>
> example sheet1:
> Col1 Col2 Col3 Col4
> abc def ghi jkl
> bbb ddd ggg jjj
> bac fed ghi klj
>
> eg sheet2:
> ColX ColY
> ghi jkl
> ghi klj
>
> so if i have a new data enter in Col3 as ghi in sheet1 then sheet2 will
> auto have that new record in ColX n ColY!! that is something like
> linking 2 sheet..
>
>
> --
> lsy
> ------------------------------------------------------------------------
> lsy's Profile:
> http://www.excelforum.com/member.php...o&userid=23917
> View this thread: http://www.excelforum.com/showthread...hreadid=375546
>

[Max]

"lsy" wrote
> yes.. this is exact what i want!! thanks...

Glad to hear that ..

> can u please explain to me what
>
<=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!C:C,MATCH(SM
ALL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))
>> means??? so that i can reuse it!!

The construct functions along these lines ..

In Sheet1:

Col F is a helper col where we frame up the "filter" criteria to pick out
values in the key col C which will match with what is input in Sheet2's A1.

Matched values in col C will return arbitrary row numbers in col F. These
arb row numbers in col F will in turn be read by the extract formulas in
Sheet2

TRIM is used to improve robustness in the matching. It'll remove extraneous
space(s) present - if any- which might otherwise throw valid matching cases
off.

In Sheet2:

The core formula placed in X2 is the
INDEX(Sheet1!C:C,MATCH(SMALL(...),...)).

SMALL(...) points to the criteria col F in Sheet1, and returns an ascending
sort of the arbitrary row numbers in col F, with any blanks thrown below.

MATCH(SMALL(...),...)) then returns the positions of the values returned by
SMALL(...) matched against the lookup_array, which again is Sheet1's col F.

INDEX(Sheet1!C:C, ..) then simply retrieves the corresponding values from
Sheet1's col C. INDEX(Sheet1!C:C, ..) in X2 copied across to Y2 will result
in INDEX(Sheet1!D:D, ..), i.e. increment the col C to D, hence returning
corresponding values from col D in Sheet1 into col Y.

Remember to fix the references to Sheet1's col F in the formula, i.e. use
Sheet1!$F:$F (with the dollar signs).

The error trap " IF(ISERROR(SMALL(...),"",INDEX(...)) " is used to return
blanks: "" (instead of #NUM!s) for a cleaner "blank" look once the returns
for all the arb row numbers from Sheet1's col F have been exhausted in
Sheet2

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"lsy" wrote:
> i still not very clear with the solution... what i want is anything key
> in in the col3 with the data with ghi in sheet 1 will auto reflect a
> record in sheet2... is that possible??

That's exactly what the suggested construct does !

Here's a sample file with the construct implemented:
http://flypicture.com/p.cfm?id=56960

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: lsy_wksht_1.xls

Play with the file. Try adding further to the sample data in Sheet1. The
results will be updated in Sheet2 automatically.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

Perhaps something along these lines ..

In Sheet1, data is assumed in cols A to D, from row1 down
with the key column being col C

Put in say, F1: =IF(TRIM(C1)="","",IF(TRIM(C1)=Sheet2!$X$1,ROW(),""))
Copy F1 down to say F100, to cover the max expected data in col C

In Sheet2
------------
Let's reserve X1 for input of the item of interest
Input in X1: ghi

Put in X2:
=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!C:C,MATCH(SMA
LL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))

Copy X2 across to Y2, fill down to Y101
(cover the same range as in col F in Sheet1)

For the input in X1,
you'll get the desired filter results in cols X and Y, from row2 down

And the results in Sheet2 will auto-update for any subsequent changes (e.g.:
new data input) made within Sheet1's col C
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"lsy" <[email protected]> wrote in message
news:[email protected]...
>
> In excel there is a fucntion that can filter particular data in the
> current page... but can i filter and display in another page!!
>
> example sheet1:
> Col1 Col2 Col3 Col4
> abc def ghi jkl
> bbb ddd ggg jjj
> bac fed ghi klj
>
> eg sheet2:
> ColX ColY
> ghi jkl
> ghi klj
>
> so if i have a new data enter in Col3 as ghi in sheet1 then sheet2 will
> auto have that new record in ColX n ColY!! that is something like
> linking 2 sheet..
>
>
> --
> lsy
> ------------------------------------------------------------------------
> lsy's Profile:
http://www.excelforum.com/member.php...o&userid=23917
> View this thread: http://www.excelforum.com/showthread...hreadid=375546
>

[Max]

Perhaps better to TRIM the input from Sheet2's X1 as well,
so put instead in F1 in Sheet1, and copy down:
=IF(TRIM(C1)="","",IF(TRIM(C1)=TRIM(Sheet2!$X$1),ROW(),""))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

Perhaps something along these lines ..

In Sheet1, data is assumed in cols A to D, from row1 down
with the key column being col C

Put in say, F1: =IF(TRIM(C1)="","",IF(TRIM(C1)=Sheet2!$X$1,ROW(),""))
Copy F1 down to say F100, to cover the max expected data in col C

In Sheet2
------------
Let's reserve X1 for input of the item of interest
Input in X1: ghi

Put in X2:
=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!C:C,MATCH(SMA
LL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))

Copy X2 across to Y2, fill down to Y101
(cover the same range as in col F in Sheet1)

For the input in X1,
you'll get the desired filter results in cols X and Y, from row2 down

And the results in Sheet2 will auto-update for any subsequent changes (e.g.:
new data input) made within Sheet1's col C
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"lsy" <[email protected]> wrote in message
news:[email protected]...
>
> In excel there is a fucntion that can filter particular data in the
> current page... but can i filter and display in another page!!
>
> example sheet1:
> Col1 Col2 Col3 Col4
> abc def ghi jkl
> bbb ddd ggg jjj
> bac fed ghi klj
>
> eg sheet2:
> ColX ColY
> ghi jkl
> ghi klj
>
> so if i have a new data enter in Col3 as ghi in sheet1 then sheet2 will
> auto have that new record in ColX n ColY!! that is something like
> linking 2 sheet..
>
>
> --
> lsy
> ------------------------------------------------------------------------
> lsy's Profile:
http://www.excelforum.com/member.php...o&userid=23917
> View this thread: http://www.excelforum.com/showthread...hreadid=375546
>

[KL]

Hi lsy,

You can also explore the functionality of the Advanced Filter (menu
Data>Filter>Advanced Filter). You may need to create a criteria range which
should be vertical and include the column header (exactly as in the original
table) and at least one row for filtering criteria for each column you want
filter by. For example:

if your database has the following columns:
ID, Name, Sales, Month

then if you create the following criteria range on the output sheet:
[A1]="Sales"
[A2]=>100
[B1]="Month"
[B2]="Jan"

the Advanced Filter output will show all sales higher than 100 in January.

In order to use Advanced Filter on a sheet other than the database, first
activate the destination sheet and then go to menu Data>Filter>Advanced
Filter. Chose the "copy to another location" option. Enter the references to
the ranges of the database "List Range" (say Sheet1!A1:D1000), "Criteria
Range" (say A1:B2 in our example) and "Copy To" (say A3 - two rows below the
criteria range) and press OK.

Regards,
KL


"lsy" <[email protected]> wrote in message
news:[email protected]...
>
> In excel there is a fucntion that can filter particular data in the
> current page... but can i filter and display in another page!!
>
> example sheet1:
> Col1 Col2 Col3 Col4
> abc def ghi jkl
> bbb ddd ggg jjj
> bac fed ghi klj
>
> eg sheet2:
> ColX ColY
> ghi jkl
> ghi klj
>
> so if i have a new data enter in Col3 as ghi in sheet1 then sheet2 will
> auto have that new record in ColX n ColY!! that is something like
> linking 2 sheet..
>
>
> --
> lsy
> ------------------------------------------------------------------------
> lsy's Profile:
> http://www.excelforum.com/member.php...o&userid=23917
> View this thread: http://www.excelforum.com/showthread...hreadid=375546
>

[Max]

Perhaps better to TRIM the input from Sheet2's X1 as well,
so put instead in F1 in Sheet1, and copy down:
=IF(TRIM(C1)="","",IF(TRIM(C1)=TRIM(Sheet2!$X$1),ROW(),""))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"lsy" wrote:
> i still not very clear with the solution... what i want is anything key
> in in the col3 with the data with ghi in sheet 1 will auto reflect a
> record in sheet2... is that possible??

That's exactly what the suggested construct does !

Here's a sample file with the construct implemented:
http://flypicture.com/p.cfm?id=56960

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: lsy_wksht_1.xls

Play with the file. Try adding further to the sample data in Sheet1. The
results will be updated in Sheet2 automatically.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"lsy" wrote
> yes.. this is exact what i want!! thanks...

Glad to hear that ..

> can u please explain to me what
>
<=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!C:C,MATCH(SM
ALL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))
>> means??? so that i can reuse it!!

The construct functions along these lines ..

In Sheet1:

Col F is a helper col where we frame up the "filter" criteria to pick out
values in the key col C which will match with what is input in Sheet2's A1.

Matched values in col C will return arbitrary row numbers in col F. These
arb row numbers in col F will in turn be read by the extract formulas in
Sheet2

TRIM is used to improve robustness in the matching. It'll remove extraneous
space(s) present - if any- which might otherwise throw valid matching cases
off.

In Sheet2:

The core formula placed in X2 is the
INDEX(Sheet1!C:C,MATCH(SMALL(...),...)).

SMALL(...) points to the criteria col F in Sheet1, and returns an ascending
sort of the arbitrary row numbers in col F, with any blanks thrown below.

MATCH(SMALL(...),...)) then returns the positions of the values returned by
SMALL(...) matched against the lookup_array, which again is Sheet1's col F.

INDEX(Sheet1!C:C, ..) then simply retrieves the corresponding values from
Sheet1's col C. INDEX(Sheet1!C:C, ..) in X2 copied across to Y2 will result
in INDEX(Sheet1!D:D, ..), i.e. increment the col C to D, hence returning
corresponding values from col D in Sheet1 into col Y.

Remember to fix the references to Sheet1's col F in the formula, i.e. use
Sheet1!$F:$F (with the dollar signs).

The error trap " IF(ISERROR(SMALL(...),"",INDEX(...)) " is used to return
blanks: "" (instead of #NUM!s) for a cleaner "blank" look once the returns
for all the arb row numbers from Sheet1's col F have been exhausted in
Sheet2

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[KL]

Hi lsy,

You can also explore the functionality of the Advanced Filter (menu
Data>Filter>Advanced Filter). You may need to create a criteria range which
should be vertical and include the column header (exactly as in the original
table) and at least one row for filtering criteria for each column you want
filter by. For example:

if your database has the following columns:
ID, Name, Sales, Month

then if you create the following criteria range on the output sheet:
[A1]="Sales"
[A2]=>100
[B1]="Month"
[B2]="Jan"

the Advanced Filter output will show all sales higher than 100 in January.

In order to use Advanced Filter on a sheet other than the database, first
activate the destination sheet and then go to menu Data>Filter>Advanced
Filter. Chose the "copy to another location" option. Enter the references to
the ranges of the database "List Range" (say Sheet1!A1:D1000), "Criteria
Range" (say A1:B2 in our example) and "Copy To" (say A3 - two rows below the
criteria range) and press OK.

Regards,
KL


"lsy" <[email protected]> wrote in message
news:[email protected]...
>
> In excel there is a fucntion that can filter particular data in the
> current page... but can i filter and display in another page!!
>
> example sheet1:
> Col1 Col2 Col3 Col4
> abc def ghi jkl
> bbb ddd ggg jjj
> bac fed ghi klj
>
> eg sheet2:
> ColX ColY
> ghi jkl
> ghi klj
>
> so if i have a new data enter in Col3 as ghi in sheet1 then sheet2 will
> auto have that new record in ColX n ColY!! that is something like
> linking 2 sheet..
>
>
> --
> lsy
> ------------------------------------------------------------------------
> lsy's Profile:
> http://www.excelforum.com/member.php...o&userid=23917
> View this thread: http://www.excelforum.com/showthread...hreadid=375546
>

[Max]

"lsy" wrote
> yes.. this is exact what i want!! thanks...

Glad to hear that ..

> can u please explain to me what
>
<=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!C:C,MATCH(SM
ALL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))
>> means??? so that i can reuse it!!

The construct functions along these lines ..

In Sheet1:

Col F is a helper col where we frame up the "filter" criteria to pick out
values in the key col C which will match with what is input in Sheet2's A1.

Matched values in col C will return arbitrary row numbers in col F. These
arb row numbers in col F will in turn be read by the extract formulas in
Sheet2

TRIM is used to improve robustness in the matching. It'll remove extraneous
space(s) present - if any- which might otherwise throw valid matching cases
off.

In Sheet2:

The core formula placed in X2 is the
INDEX(Sheet1!C:C,MATCH(SMALL(...),...)).

SMALL(...) points to the criteria col F in Sheet1, and returns an ascending
sort of the arbitrary row numbers in col F, with any blanks thrown below.

MATCH(SMALL(...),...)) then returns the positions of the values returned by
SMALL(...) matched against the lookup_array, which again is Sheet1's col F.

INDEX(Sheet1!C:C, ..) then simply retrieves the corresponding values from
Sheet1's col C. INDEX(Sheet1!C:C, ..) in X2 copied across to Y2 will result
in INDEX(Sheet1!D:D, ..), i.e. increment the col C to D, hence returning
corresponding values from col D in Sheet1 into col Y.

Remember to fix the references to Sheet1's col F in the formula, i.e. use
Sheet1!$F:$F (with the dollar signs).

The error trap " IF(ISERROR(SMALL(...),"",INDEX(...)) " is used to return
blanks: "" (instead of #NUM!s) for a cleaner "blank" look once the returns
for all the arb row numbers from Sheet1's col F have been exhausted in
Sheet2

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"lsy" wrote:
> i still not very clear with the solution... what i want is anything key
> in in the col3 with the data with ghi in sheet 1 will auto reflect a
> record in sheet2... is that possible??

That's exactly what the suggested construct does !

Here's a sample file with the construct implemented:
http://flypicture.com/p.cfm?id=56960

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: lsy_wksht_1.xls

Play with the file. Try adding further to the sample data in Sheet1. The
results will be updated in Sheet2 automatically.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

Perhaps better to TRIM the input from Sheet2's X1 as well,
so put instead in F1 in Sheet1, and copy down:
=IF(TRIM(C1)="","",IF(TRIM(C1)=TRIM(Sheet2!$X$1),ROW(),""))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

Perhaps something along these lines ..

In Sheet1, data is assumed in cols A to D, from row1 down
with the key column being col C

Put in say, F1: =IF(TRIM(C1)="","",IF(TRIM(C1)=Sheet2!$X$1,ROW(),""))
Copy F1 down to say F100, to cover the max expected data in col C

In Sheet2
------------
Let's reserve X1 for input of the item of interest
Input in X1: ghi

Put in X2:
=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!C:C,MATCH(SMA
LL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))

Copy X2 across to Y2, fill down to Y101
(cover the same range as in col F in Sheet1)

For the input in X1,
you'll get the desired filter results in cols X and Y, from row2 down

And the results in Sheet2 will auto-update for any subsequent changes (e.g.:
new data input) made within Sheet1's col C
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"lsy" <[email protected]> wrote in message
news:[email protected]...
>
> In excel there is a fucntion that can filter particular data in the
> current page... but can i filter and display in another page!!
>
> example sheet1:
> Col1 Col2 Col3 Col4
> abc def ghi jkl
> bbb ddd ggg jjj
> bac fed ghi klj
>
> eg sheet2:
> ColX ColY
> ghi jkl
> ghi klj
>
> so if i have a new data enter in Col3 as ghi in sheet1 then sheet2 will
> auto have that new record in ColX n ColY!! that is something like
> linking 2 sheet..
>
>
> --
> lsy
> ------------------------------------------------------------------------
> lsy's Profile:
http://www.excelforum.com/member.php...o&userid=23917
> View this thread: http://www.excelforum.com/showthread...hreadid=375546
>

[Max]

Perhaps something along these lines ..

In Sheet1, data is assumed in cols A to D, from row1 down
with the key column being col C

Put in say, F1: =IF(TRIM(C1)="","",IF(TRIM(C1)=Sheet2!$X$1,ROW(),""))
Copy F1 down to say F100, to cover the max expected data in col C

In Sheet2
------------
Let's reserve X1 for input of the item of interest
Input in X1: ghi

Put in X2:
=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!C:C,MATCH(SMA
LL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))

Copy X2 across to Y2, fill down to Y101
(cover the same range as in col F in Sheet1)

For the input in X1,
you'll get the desired filter results in cols X and Y, from row2 down

And the results in Sheet2 will auto-update for any subsequent changes (e.g.:
new data input) made within Sheet1's col C
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"lsy" <[email protected]> wrote in message
news:[email protected]...
>
> In excel there is a fucntion that can filter particular data in the
> current page... but can i filter and display in another page!!
>
> example sheet1:
> Col1 Col2 Col3 Col4
> abc def ghi jkl
> bbb ddd ggg jjj
> bac fed ghi klj
>
> eg sheet2:
> ColX ColY
> ghi jkl
> ghi klj
>
> so if i have a new data enter in Col3 as ghi in sheet1 then sheet2 will
> auto have that new record in ColX n ColY!! that is something like
> linking 2 sheet..
>
>
> --
> lsy
> ------------------------------------------------------------------------
> lsy's Profile:
http://www.excelforum.com/member.php...o&userid=23917
> View this thread: http://www.excelforum.com/showthread...hreadid=375546
>

[KL]

Hi lsy,

You can also explore the functionality of the Advanced Filter (menu
Data>Filter>Advanced Filter). You may need to create a criteria range which
should be vertical and include the column header (exactly as in the original
table) and at least one row for filtering criteria for each column you want
filter by. For example:

if your database has the following columns:
ID, Name, Sales, Month

then if you create the following criteria range on the output sheet:
[A1]="Sales"
[A2]=>100
[B1]="Month"
[B2]="Jan"

the Advanced Filter output will show all sales higher than 100 in January.

In order to use Advanced Filter on a sheet other than the database, first
activate the destination sheet and then go to menu Data>Filter>Advanced
Filter. Chose the "copy to another location" option. Enter the references to
the ranges of the database "List Range" (say Sheet1!A1:D1000), "Criteria
Range" (say A1:B2 in our example) and "Copy To" (say A3 - two rows below the
criteria range) and press OK.

Regards,
KL


"lsy" <[email protected]> wrote in message
news:[email protected]...
>
> In excel there is a fucntion that can filter particular data in the
> current page... but can i filter and display in another page!!
>
> example sheet1:
> Col1 Col2 Col3 Col4
> abc def ghi jkl
> bbb ddd ggg jjj
> bac fed ghi klj
>
> eg sheet2:
> ColX ColY
> ghi jkl
> ghi klj
>
> so if i have a new data enter in Col3 as ghi in sheet1 then sheet2 will
> auto have that new record in ColX n ColY!! that is something like
> linking 2 sheet..
>
>
> --
> lsy
> ------------------------------------------------------------------------
> lsy's Profile:
> http://www.excelforum.com/member.php...o&userid=23917
> View this thread: http://www.excelforum.com/showthread...hreadid=375546
>

[Max]

Perhaps better to TRIM the input from Sheet2's X1 as well,
so put instead in F1 in Sheet1, and copy down:
=IF(TRIM(C1)="","",IF(TRIM(C1)=TRIM(Sheet2!$X$1),ROW(),""))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"lsy" wrote:
> i still not very clear with the solution... what i want is anything key
> in in the col3 with the data with ghi in sheet 1 will auto reflect a
> record in sheet2... is that possible??

That's exactly what the suggested construct does !

Here's a sample file with the construct implemented:
http://flypicture.com/p.cfm?id=56960

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: lsy_wksht_1.xls

Play with the file. Try adding further to the sample data in Sheet1. The
results will be updated in Sheet2 automatically.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"lsy" wrote
> yes.. this is exact what i want!! thanks...

Glad to hear that ..

> can u please explain to me what
>
<=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!C:C,MATCH(SM
ALL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))
>> means??? so that i can reuse it!!

The construct functions along these lines ..

In Sheet1:

Col F is a helper col where we frame up the "filter" criteria to pick out
values in the key col C which will match with what is input in Sheet2's A1.

Matched values in col C will return arbitrary row numbers in col F. These
arb row numbers in col F will in turn be read by the extract formulas in
Sheet2

TRIM is used to improve robustness in the matching. It'll remove extraneous
space(s) present - if any- which might otherwise throw valid matching cases
off.

In Sheet2:

The core formula placed in X2 is the
INDEX(Sheet1!C:C,MATCH(SMALL(...),...)).

SMALL(...) points to the criteria col F in Sheet1, and returns an ascending
sort of the arbitrary row numbers in col F, with any blanks thrown below.

MATCH(SMALL(...),...)) then returns the positions of the values returned by
SMALL(...) matched against the lookup_array, which again is Sheet1's col F.

INDEX(Sheet1!C:C, ..) then simply retrieves the corresponding values from
Sheet1's col C. INDEX(Sheet1!C:C, ..) in X2 copied across to Y2 will result
in INDEX(Sheet1!D:D, ..), i.e. increment the col C to D, hence returning
corresponding values from col D in Sheet1 into col Y.

Remember to fix the references to Sheet1's col F in the formula, i.e. use
Sheet1!$F:$F (with the dollar signs).

The error trap " IF(ISERROR(SMALL(...),"",INDEX(...)) " is used to return
blanks: "" (instead of #NUM!s) for a cleaner "blank" look once the returns
for all the arb row numbers from Sheet1's col F have been exhausted in
Sheet2

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----