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

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 <atyahoo<dotcom
----
"lsy" wrote in message
...

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

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),R OW(),""))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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??


--
lsy
------------------------------------------------------------------------
lsy's Profile: http://www.excelforum.com/member.php...o&userid=23917
View this thread: http://www.excelforum.com/showthread...hreadid=375546

"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 <atyahoo<dotcom
----

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..

Max Wrote:
"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 <atyahoo<dotcom
----


--
lsy
------------------------------------------------------------------------
lsy's Profile: http://www.excelforum.com/member.php...o&userid=23917
View this thread: http://www.excelforum.com/showthread...hreadid=375546

Hi lsy,

You can also explore the functionality of the Advanced Filter (menu
DataFilterAdvanced 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 DataFilterAdvanced
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" wrote in message
...

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