Thx for this , I have used it in a programme I am using for defecting
equipment, i.e work outstanding or completed. I was wondering if I could add
another argument into the formula? I have a "remarks" column and if
"SCRAPPED" is added could it be moved to another sheet named "Scrapped"
(funnily enough)? I have tried but can't seem to get it to work.
Thx in advance
Kev

"Max" wrote:

Here's a non array formulas automation approach to play with ..

Assume source data as below is maintained in sheet: Data,
data from row2 down (Quit is col D, where you'd enter "Y")

StudentName Age DateJoin Quit
Name1 Age1 date1 Y
Name2 Age2 date2
Name3 Age3 date3 Y
Name4 Age4 date4
etc

Put in E2: =IF(D2="Y",ROW(),"")
Put in F2: =IF(OR(D2="Y",A2=""),"",ROW())
Select E2:F2, copy down to say, F100,
to cover the max expected data range

In sheet: Completed
------------
With the headers pasted into A1:C1, viz.:
StudentName Age DateJoin

Put in A2:
=IF(ISERROR(SMALL(Data!$E:$E,ROWS($A$1:A1))),"",
INDEX(Data!A:A,
MATCH(SMALL(Data!$E:$E,ROWS($A$1:A1)),Data!$E:$E,0 )))

Copy A2 across to C2, fill down to C100
(cover the same range as per cols E & F in "Data")

Format col C as date

"Completed" will contain only those lines from "Data" which have the "Y"
marked in col D in "Data", all neatly bunched at the top

Now make a copy of "Completed", rename it as "Current"

In sheet: Current
------------
Replace the formula in A2 with:

=IF(ISERROR(SMALL(Data!$F:$F,ROWS($A$1:A1))),"",
INDEX(Data!A:A,
MATCH(SMALL(Data!$F:$F,ROWS($A$1:A1)),Data!$F:$F,0 )))

(same formula basically, but pointing now to col F in "Data", instead of col
E)

Copy A2 across to C2, fill down to C100
Format col C as date

"Current" will contain only those lines from "Data" which do not have the
"Y" marked in col D in "Data", all neatly bunched at the top

--
So you could maintain the continuous details in "Data", and the desired
results will be auto-output in "Current" and "Completed"
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Freshman" wrote in message
...
Dear all,

I've created a workbook containing 2 worksheets namely, "Current" and
"Completed". My idea is using "Current" to contain existing records and
"Completed" to contain completed records. In "Current" worksheet, there
are
rows of records with 3 columns headings. For example:

Student Name Age Date Join

My question is, if I add "Quit" as the 4th column heading, then if I fill
"Y" next to any records, the records will be removed from "Current"
worksheet
immediately and update to the last record of "Completed" worksheet
automatically.

Can any worksheet functions or VBE codes be done? If yes, please kindly
advise.

Many many thanks.