moving filtered data between worksheets automatically

[dweezy]

I am not sure if this can be done.
I have a database in excel that tracks certain equipment inventory and
maintenance .

worksheet 1 looks like this:
Truck # Station Location code # Status DATE
450 Millersville dw56 repair 10/3/06
451 Tioga dw63 inventory 10/1/06
452 Baltimore dw78 repair 9/30/06


I would like for any thing that has Status = repair to automatically go into
worksheet#2. Also in worsheet 1 there are drop down lists for the truck,
Station location, and Status (not sure if that effects what I want to do).
Any help is greatly appreciated.
thanks

 

[vezerid]

Hi,
A way for automatically transferring the data to sheet2 is to implant
formulas in enough rows to hold all the data (and copy the formulas
further down when they fill).

If you put in Sheet2!A2 the following formula, you can copy it through
5 columns and as many rows as necessary.

=IF(ISNUMBER(SMALL(IF(Sheet1!$D$1:$D$100="repair",ROW(Sheet1!$A$1:$A$100)),ROW()-ROW($A$2)+1)),INDEX(Sheet1!A$1:A$100,SMALL(IF(Sheet1!$D$1:$D$100="a",ROW(Sheet1!$A$1:$A$100)),ROW()-ROW($A$2)+1)),"")

This is an array formula so it must be committed with Shift+Ctrl+Enter.

HTH
Kostis Vezerides

 

[dweezy]

Thanks so much, I have not gotten it to work yet. I don't have much time now
to play with it. I will try later and update my progress.
-d

 

[dweezy]

Vezrid,
I did not get the formula to work, in addition the database I am filtering
and the info I am trying to move has way more than 5 columns. Thanks for the
suggestion.

I am thinking that I will need to write some type of macro. Problem is, I
have no experience doing that.
Anybody please help!