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.

You could use a sheet change event. Right click the Current sheet tab,
select View Code and paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
If Target.Count = 1 And Target.Column = 4 And Target.Value = "Y" Then
Dim eRow As Long
eRow = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1
Range(Cells(Target.Row, 1), Cells(Target.Row, 4)).Copy _
Sheets("Completed").Cells(eRow, 1)
Rows(Target.Row).Delete
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

Hope this helps
Rowan

Freshman wrote:
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.

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.

Dear Roman,

Thanks for your help and it works. On minor problem is if I enter "Y" in a
cell and want to copy down to the subsequence rows by dragging the handler,
only the first row record is removed to "Completed" worksheet, the others
have "Y" in the cells but the records are still there. Any ways to improve,
please advise.

Thanks again.

"Rowan" wrote:

You could use a sheet change event. Right click the Current sheet tab,
select View Code and paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
If Target.Count = 1 And Target.Column = 4 And Target.Value = "Y" Then
Dim eRow As Long
eRow = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1
Range(Cells(Target.Row, 1), Cells(Target.Row, 4)).Copy _
Sheets("Completed").Cells(eRow, 1)
Rows(Target.Row).Delete
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

Hope this helps
Rowan

Freshman wrote:
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.

Hi Max,

Happy to talk to you here again and your way is working fine too.

Best regards.

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

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Freshman" wrote in message
...
Hi Max,

Happy to talk to you here again and your way is working fine too.

Best regards.

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.

Assuming in the source table (in sheet: Data)
the Remarks col is col E, data from row2 down

In Sheet: Scrapped

The same col headers are in A1:E1

Put in A2:
=IF(ISERROR(SMALL($F:$F,ROWS($A$1:A1))),"",
INDEX(Data!A:A,MATCH(SMALL($F:$F,ROWS($A$1:A1)),$F :$F,0)))
Copy A2 across to E2

Put in F2:
=IF(Data!E2="","",IF(Data!E2="Scrapped",ROW(),""))
(Leave F1 empty)

Select A2:F2, fill down as far as required
to cover the max expected extent of source data

Sheet: Scrapped will return only those lines from Data
with "Scrapped" in the Remarks col, all neatly bunched at the top

Adapt to suit ..
--
And if the criteria is to look at 2 cols, say Status (col D = "Y") and
Remarks (col E = "Scrapped"), just amend the criteria formula in F2 above
to:

=IF(OR(Data!D2="",Data!E2=""),"",IF(AND(Data!D2="Y ",Data!E2="Scrapped"),ROW(
),""))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kevhatch" wrote in message
...
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

Thx Max, just the job.... You guys r great

"Max" wrote:

Assuming in the source table (in sheet: Data)
the Remarks col is col E, data from row2 down

In Sheet: Scrapped

The same col headers are in A1:E1

Put in A2:
=IF(ISERROR(SMALL($F:$F,ROWS($A$1:A1))),"",
INDEX(Data!A:A,MATCH(SMALL($F:$F,ROWS($A$1:A1)),$F :$F,0)))
Copy A2 across to E2

Put in F2:
=IF(Data!E2="","",IF(Data!E2="Scrapped",ROW(),""))
(Leave F1 empty)

Select A2:F2, fill down as far as required
to cover the max expected extent of source data

Sheet: Scrapped will return only those lines from Data
with "Scrapped" in the Remarks col, all neatly bunched at the top

Adapt to suit ..
--
And if the criteria is to look at 2 cols, say Status (col D = "Y") and
Remarks (col E = "Scrapped"), just amend the criteria formula in F2 above
to:

=IF(OR(Data!D2="",Data!E2=""),"",IF(AND(Data!D2="Y ",Data!E2="Scrapped"),ROW(
),""))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kevhatch" wrote in message
...
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

Glad it worked for you !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kevhatch" wrote in message
...
Thx Max, just the job.... You guys r great