Conditional formula
 

I have a task spreadsheet. When my task changes to complete I want to pull
to another sheet and hide it's view from the main sheet. Does anyone have
any suggestions?

Conditional formula
 

You mention a task sheet and a Main sheet.

"Pull" what to which "other sheet"

How will you know when the task is complete and which cells would be copied or
cut to the other sheet to be hidden?

Have you done this manually?

If so, maybe record a macro whilst doing it.

Or possibly event code to do it automatically when a certain cell turns to "task
complete".


Gord Dibben MS Excel MVP


On Tue, 10 Jun 2008 11:48:02 -0700, Mindy
wrote:

I have a task spreadsheet. When my task changes to complete I want to pull
to another sheet and hide it's view from the main sheet. Does anyone have
any suggestions?

Conditional formula
 

I have a main spreadsheet with 7 columns. (I have created a list to sort
through tasks, action officers, etc.) One of those columns is status. I
have put in drop down lists to pick the status ( complete, new, working..)
When Complete is selected I would like the column to hide itself and then
populated another sheet that has all the completed tasks(only) in it.

"Gord Dibben" wrote:

You mention a task sheet and a Main sheet.

"Pull" what to which "other sheet"

How will you know when the task is complete and which cells would be copied or
cut to the other sheet to be hidden?

Have you done this manually?

If so, maybe record a macro whilst doing it.

Or possibly event code to do it automatically when a certain cell turns to "task
complete".


Gord Dibben MS Excel MVP


On Tue, 10 Jun 2008 11:48:02 -0700, Mindy
wrote:

I have a task spreadsheet. When my task changes to complete I want to pull
to another sheet and hide it's view from the main sheet. Does anyone have
any suggestions?

Conditional formula
 

You want the column to hide itself or the row to hide itself?

I would say the row to be hidden and copied

For the row hiding see your other posting that you tacked onto someone else's
original.

To both copy the row to another sheet and hide the row use this code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub


Gord

On Tue, 10 Jun 2008 12:37:00 -0700, Mindy
wrote:

I have a main spreadsheet with 7 columns. (I have created a list to sort
through tasks, action officers, etc.) One of those columns is status. I
have put in drop down lists to pick the status ( complete, new, working..)
When Complete is selected I would like the column to hide itself and then
populated another sheet that has all the completed tasks(only) in it.

"Gord Dibben" wrote:

You mention a task sheet and a Main sheet.

"Pull" what to which "other sheet"

How will you know when the task is complete and which cells would be copied or
cut to the other sheet to be hidden?

Have you done this manually?

If so, maybe record a macro whilst doing it.

Or possibly event code to do it automatically when a certain cell turns to "task
complete".


Gord Dibben MS Excel MVP


On Tue, 10 Jun 2008 11:48:02 -0700, Mindy
wrote:

I have a task spreadsheet. When my task changes to complete I want to pull
to another sheet and hide it's view from the main sheet. Does anyone have
any suggestions?

Conditional formula
 

One other thing I should mention.

You can hide the "Completed" sheet and it will still be updated.


Gord

On Tue, 10 Jun 2008 14:41:06 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

You want the column to hide itself or the row to hide itself?

I would say the row to be hidden and copied

For the row hiding see your other posting that you tacked onto someone else's
original.

To both copy the row to another sheet and hide the row use this code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub


Gord

On Tue, 10 Jun 2008 12:37:00 -0700, Mindy
wrote:

I have a main spreadsheet with 7 columns. (I have created a list to sort
through tasks, action officers, etc.) One of those columns is status. I
have put in drop down lists to pick the status ( complete, new, working..)
When Complete is selected I would like the column to hide itself and then
populated another sheet that has all the completed tasks(only) in it.

"Gord Dibben" wrote:

You mention a task sheet and a Main sheet.

"Pull" what to which "other sheet"

How will you know when the task is complete and which cells would be copied or
cut to the other sheet to be hidden?

Have you done this manually?

If so, maybe record a macro whilst doing it.

Or possibly event code to do it automatically when a certain cell turns to "task
complete".


Gord Dibben MS Excel MVP


On Tue, 10 Jun 2008 11:48:02 -0700, Mindy
wrote:

I have a task spreadsheet. When my task changes to complete I want to pull
to another sheet and hide it's view from the main sheet. Does anyone have
any suggestions?

Conditional formula
 

Yes, that is exactly what I wanted it to do. However, I never had to run a
macro in excel, and I am not sure how or what is the best way to do it. Can
you walk me thru some steps? Thanks a bunch!

"Gord Dibben" wrote:

One other thing I should mention.

You can hide the "Completed" sheet and it will still be updated.


Gord

On Tue, 10 Jun 2008 14:41:06 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

You want the column to hide itself or the row to hide itself?

I would say the row to be hidden and copied

For the row hiding see your other posting that you tacked onto someone else's
original.

To both copy the row to another sheet and hide the row use this code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub


Gord

On Tue, 10 Jun 2008 12:37:00 -0700, Mindy
wrote:

I have a main spreadsheet with 7 columns. (I have created a list to sort
through tasks, action officers, etc.) One of those columns is status. I
have put in drop down lists to pick the status ( complete, new, working..)
When Complete is selected I would like the column to hide itself and then
populated another sheet that has all the completed tasks(only) in it.

"Gord Dibben" wrote:

You mention a task sheet and a Main sheet.

"Pull" what to which "other sheet"

How will you know when the task is complete and which cells would be copied or
cut to the other sheet to be hidden?

Have you done this manually?

If so, maybe record a macro whilst doing it.

Or possibly event code to do it automatically when a certain cell turns to "task
complete".


Gord Dibben MS Excel MVP


On Tue, 10 Jun 2008 11:48:02 -0700, Mindy
wrote:

I have a task spreadsheet. When my task changes to complete I want to pull
to another sheet and hide it's view from the main sheet. Does anyone have
any suggestions?

Conditional formula
 

I explained all that in the reply to your other post but maybe you lost track of
that because it was not your original post.

Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Edit to suit..........target.cells.column = 6 means column F

You must have a sheet named Completed or edit that also in the code.

Alt + q to return to your Excel sheet.

Enter "complete"(no quotes) in a cell in column F and that row will be copied to
the Completed sheet at next available row.


Gord

On Wed, 11 Jun 2008 03:59:01 -0700, Mindy
wrote:

Yes, that is exactly what I wanted it to do. However, I never had to run a
macro in excel, and I am not sure how or what is the best way to do it. Can
you walk me thru some steps? Thanks a bunch!

"Gord Dibben" wrote:

One other thing I should mention.

You can hide the "Completed" sheet and it will still be updated.


Gord

On Tue, 10 Jun 2008 14:41:06 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

You want the column to hide itself or the row to hide itself?

I would say the row to be hidden and copied

For the row hiding see your other posting that you tacked onto someone else's
original.

To both copy the row to another sheet and hide the row use this code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub


Gord

On Tue, 10 Jun 2008 12:37:00 -0700, Mindy
wrote:

I have a main spreadsheet with 7 columns. (I have created a list to sort
through tasks, action officers, etc.) One of those columns is status. I
have put in drop down lists to pick the status ( complete, new, working..)
When Complete is selected I would like the column to hide itself and then
populated another sheet that has all the completed tasks(only) in it.

"Gord Dibben" wrote:

You mention a task sheet and a Main sheet.

"Pull" what to which "other sheet"

How will you know when the task is complete and which cells would be copied or
cut to the other sheet to be hidden?

Have you done this manually?

If so, maybe record a macro whilst doing it.

Or possibly event code to do it automatically when a certain cell turns to "task
complete".


Gord Dibben MS Excel MVP


On Tue, 10 Jun 2008 11:48:02 -0700, Mindy
wrote:

I have a task spreadsheet. When my task changes to complete I want to pull
to another sheet and hide it's view from the main sheet. Does anyone have
any suggestions?

Conditional formula
 

Sorry, to bother you again! But I am using the below code and it do not
work. Can you see where I went wrong? Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed Tasks") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub

"Gord Dibben" wrote:

I explained all that in the reply to your other post but maybe you lost track of
that because it was not your original post.

Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Edit to suit..........target.cells.column = 6 means column F

You must have a sheet named Completed or edit that also in the code.

Alt + q to return to your Excel sheet.

Enter "complete"(no quotes) in a cell in column F and that row will be copied to
the Completed sheet at next available row.


Gord

On Wed, 11 Jun 2008 03:59:01 -0700, Mindy
wrote:

Yes, that is exactly what I wanted it to do. However, I never had to run a
macro in excel, and I am not sure how or what is the best way to do it. Can
you walk me thru some steps? Thanks a bunch!

"Gord Dibben" wrote:

One other thing I should mention.

You can hide the "Completed" sheet and it will still be updated.


Gord

On Tue, 10 Jun 2008 14:41:06 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

You want the column to hide itself or the row to hide itself?

I would say the row to be hidden and copied

For the row hiding see your other posting that you tacked onto someone else's
original.

To both copy the row to another sheet and hide the row use this code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub


Gord

On Tue, 10 Jun 2008 12:37:00 -0700, Mindy
wrote:

I have a main spreadsheet with 7 columns. (I have created a list to sort
through tasks, action officers, etc.) One of those columns is status. I
have put in drop down lists to pick the status ( complete, new, working..)
When Complete is selected I would like the column to hide itself and then
populated another sheet that has all the completed tasks(only) in it.

"Gord Dibben" wrote:

You mention a task sheet and a Main sheet.

"Pull" what to which "other sheet"

How will you know when the task is complete and which cells would be copied or
cut to the other sheet to be hidden?

Have you done this manually?

If so, maybe record a macro whilst doing it.

Or possibly event code to do it automatically when a certain cell turns to "task
complete".


Gord Dibben MS Excel MVP


On Tue, 10 Jun 2008 11:48:02 -0700, Mindy
wrote:

I have a task spreadsheet. When my task changes to complete I want to pull
to another sheet and hide it's view from the main sheet. Does anyone have
any suggestions?

Conditional formula
 

"do(sic) not work" means what?

What do you want to happen and what is not happening?

The code was a suggestion only........you never did post any answers to my
questions in my first reply.


Gord

On Tue, 15 Jul 2008 12:43:00 -0700, Mindy
wrote:

Sorry, to bother you again! But I am using the below code and it do not
work. Can you see where I went wrong? Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed Tasks") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub

"Gord Dibben" wrote:

I explained all that in the reply to your other post but maybe you lost track of
that because it was not your original post.

Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Edit to suit..........target.cells.column = 6 means column F

You must have a sheet named Completed or edit that also in the code.

Alt + q to return to your Excel sheet.

Enter "complete"(no quotes) in a cell in column F and that row will be copied to
the Completed sheet at next available row.


Gord

On Wed, 11 Jun 2008 03:59:01 -0700, Mindy
wrote:

Yes, that is exactly what I wanted it to do. However, I never had to run a
macro in excel, and I am not sure how or what is the best way to do it. Can
you walk me thru some steps? Thanks a bunch!

"Gord Dibben" wrote:

One other thing I should mention.

You can hide the "Completed" sheet and it will still be updated.


Gord

On Tue, 10 Jun 2008 14:41:06 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

You want the column to hide itself or the row to hide itself?

I would say the row to be hidden and copied

For the row hiding see your other posting that you tacked onto someone else's
original.

To both copy the row to another sheet and hide the row use this code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub


Gord

On Tue, 10 Jun 2008 12:37:00 -0700, Mindy
wrote:

I have a main spreadsheet with 7 columns. (I have created a list to sort
through tasks, action officers, etc.) One of those columns is status. I
have put in drop down lists to pick the status ( complete, new, working..)
When Complete is selected I would like the column to hide itself and then
populated another sheet that has all the completed tasks(only) in it.

"Gord Dibben" wrote:

You mention a task sheet and a Main sheet.

"Pull" what to which "other sheet"

How will you know when the task is complete and which cells would be copied or
cut to the other sheet to be hidden?

Have you done this manually?

If so, maybe record a macro whilst doing it.

Or possibly event code to do it automatically when a certain cell turns to "task
complete".


Gord Dibben MS Excel MVP


On Tue, 10 Jun 2008 11:48:02 -0700, Mindy
wrote:

I have a task spreadsheet. When my task changes to complete I want to pull
to another sheet and hide it's view from the main sheet. Does anyone have
any suggestions?

Conditional formula
 

Gord,

Thanks for catching my typo, but what I was trying to convey was that the
code provided (as a suggestion) did not work. I was hoping you could provide
your expert knowledge to further assist me. I am sorry, if I did not respond
to your reply, but I do not recall your question. If you kindly refrain your
question, and I will be glad to respond. Sorry for any confusion this may
have caused.

To be more specific on my challenge:

I would like to hide the row automatically when the task in Column F (Column
F Named Status) states "Complete" and then copied into another sheet titled
"Completed Tasks". (FYI: Column F consists of a drop down list: Complete,
Working, New, and Waiting on someone else.)

Thank you so much for your help!


"Gord Dibben" wrote:

"do(sic) not work" means what?

What do you want to happen and what is not happening?

The code was a suggestion only........you never did post any answers to my
questions in my first reply.


Gord

On Tue, 15 Jul 2008 12:43:00 -0700, Mindy
wrote:

Sorry, to bother you again! But I am using the below code and it do not
work. Can you see where I went wrong? Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed Tasks") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub

"Gord Dibben" wrote:

I explained all that in the reply to your other post but maybe you lost track of
that because it was not your original post.

Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Edit to suit..........target.cells.column = 6 means column F

You must have a sheet named Completed or edit that also in the code.

Alt + q to return to your Excel sheet.

Enter "complete"(no quotes) in a cell in column F and that row will be copied to
the Completed sheet at next available row.


Gord

On Wed, 11 Jun 2008 03:59:01 -0700, Mindy
wrote:

Yes, that is exactly what I wanted it to do. However, I never had to run a
macro in excel, and I am not sure how or what is the best way to do it. Can
you walk me thru some steps? Thanks a bunch!

"Gord Dibben" wrote:

One other thing I should mention.

You can hide the "Completed" sheet and it will still be updated.


Gord

On Tue, 10 Jun 2008 14:41:06 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

You want the column to hide itself or the row to hide itself?

I would say the row to be hidden and copied

For the row hiding see your other posting that you tacked onto someone else's
original.

To both copy the row to another sheet and hide the row use this code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub


Gord

On Tue, 10 Jun 2008 12:37:00 -0700, Mindy
wrote:

I have a main spreadsheet with 7 columns. (I have created a list to sort
through tasks, action officers, etc.) One of those columns is status. I
have put in drop down lists to pick the status ( complete, new, working..)
When Complete is selected I would like the column to hide itself and then
populated another sheet that has all the completed tasks(only) in it.

"Gord Dibben" wrote:

You mention a task sheet and a Main sheet.

"Pull" what to which "other sheet"

How will you know when the task is complete and which cells would be copied or
cut to the other sheet to be hidden?

Have you done this manually?

If so, maybe record a macro whilst doing it.

Or possibly event code to do it automatically when a certain cell turns to "task
complete".


Gord Dibben MS Excel MVP


On Tue, 10 Jun 2008 11:48:02 -0700, Mindy
wrote:

I have a task spreadsheet. When my task changes to complete I want to pull
to another sheet and hide it's view from the main sheet. Does anyone have
any suggestions?

Conditional formula
 

See bottom of this post for my questions.

The code I posted will, if the selected cell in column F reads "completed",
copy the row to "Completed Tasks" sheet and hide the source row in source
sheet.

Sounds like what you need.

How doesn't it work?

You will have to have the Dropdown list in each cell of your usedrange in
column F


Gord

On Mon, 21 Jul 2008 07:21:00 -0700, Mindy
wrote:

Gord,

Thanks for catching my typo, but what I was trying to convey was that the
code provided (as a suggestion) did not work. I was hoping you could provide
your expert knowledge to further assist me. I am sorry, if I did not respond
to your reply, but I do not recall your question. If you kindly refrain your
question, and I will be glad to respond. Sorry for any confusion this may
have caused.

To be more specific on my challenge:

I would like to hide the row automatically when the task in Column F (Column
F Named Status) states "Complete" and then copied into another sheet titled
"Completed Tasks". (FYI: Column F consists of a drop down list: Complete,
Working, New, and Waiting on someone else.)

Thank you so much for your help!


"Gord Dibben" wrote:

"do(sic) not work" means what?

What do you want to happen and what is not happening?

The code was a suggestion only........you never did post any answers to my
questions in my first reply.


Gord

On Tue, 15 Jul 2008 12:43:00 -0700, Mindy
wrote:

Sorry, to bother you again! But I am using the below code and it do not
work. Can you see where I went wrong? Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed Tasks") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub

"Gord Dibben" wrote:

I explained all that in the reply to your other post but maybe you lost track of
that because it was not your original post.

Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Edit to suit..........target.cells.column = 6 means column F

You must have a sheet named Completed or edit that also in the code.

Alt + q to return to your Excel sheet.

Enter "complete"(no quotes) in a cell in column F and that row will be copied to
the Completed sheet at next available row.


Gord

On Wed, 11 Jun 2008 03:59:01 -0700, Mindy
wrote:

Yes, that is exactly what I wanted it to do. However, I never had to run a
macro in excel, and I am not sure how or what is the best way to do it. Can
you walk me thru some steps? Thanks a bunch!

"Gord Dibben" wrote:

One other thing I should mention.

You can hide the "Completed" sheet and it will still be updated.


Gord

On Tue, 10 Jun 2008 14:41:06 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

You want the column to hide itself or the row to hide itself?

I would say the row to be hidden and copied

For the row hiding see your other posting that you tacked onto someone else's
original.

To both copy the row to another sheet and hide the row use this code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub


Gord

On Tue, 10 Jun 2008 12:37:00 -0700, Mindy
wrote:

I have a main spreadsheet with 7 columns. (I have created a list to sort
through tasks, action officers, etc.) One of those columns is status. I
have put in drop down lists to pick the status ( complete, new, working..)
When Complete is selected I would like the column to hide itself and then
populated another sheet that has all the completed tasks(only) in it.

"Gord Dibben" wrote:

You mention a task sheet and a Main sheet.

"Pull" what to which "other sheet"

How will you know when the task is complete and which cells would be copied or
cut to the other sheet to be hidden?

Have you done this manually?

If so, maybe record a macro whilst doing it.

Or possibly event code to do it automatically when a certain cell turns to "task
complete".


Gord Dibben MS Excel MVP


On Tue, 10 Jun 2008 11:48:02 -0700, Mindy
wrote:

I have a task spreadsheet. When my task changes to complete I want to pull
to another sheet and hide it's view from the main sheet. Does anyone have
any suggestions?

Conditional formula/Please help before I throw my computer!
 

Redirecting any questions:
"Pull" what to which "other sheet"
How will you know when the task is complete and which cells would be copied
or
cut to the other sheet to be hidden?

I have a sheet titled ongoing tasks, which is populated with various tasks
and in Column F titled "Status" I have a drop down list created from a
validation range Q3:Q6). Once the task is complete and the Status has
changed in Column F titled "Complete" (by user selecting "Complete" from the
drop down list) I want the entire row to hide itself from the Ongoing Tasks
view and then autopopulate in a worksheet titled" Completed Tasks".

Have you done this manually?
If so, maybe record a macro whilst doing it.

No, I do not want to do this manually and record a macro.


Or possibly event code to do it automatically when a certain cell turns to
"task
complete".

Yes, I want to use a possibly event code to do this automatically. I
currently used the code provided below and it did not work. However, I did
not get an error message. Does anyone have any suggestions?


Gord Dibben MS Excel MVP


On Tue, 10 Jun 2008 11:48:02 -0700, Mindy
wrote:


"Gord Dibben" wrote:

See bottom of this post for my questions.

The code I posted will, if the selected cell in column F reads "completed",
copy the row to "Completed Tasks" sheet and hide the source row in source
sheet.

Sounds like what you need.

How doesn't it work?

You will have to have the Dropdown list in each cell of your usedrange in
column F


Gord

On Mon, 21 Jul 2008 07:21:00 -0700, Mindy
wrote:

Gord,

Thanks for catching my typo, but what I was trying to convey was that the
code provided (as a suggestion) did not work. I was hoping you could provide
your expert knowledge to further assist me. I am sorry, if I did not respond
to your reply, but I do not recall your question. If you kindly refrain your
question, and I will be glad to respond. Sorry for any confusion this may
have caused.

To be more specific on my challenge:

I would like to hide the row automatically when the task in Column F (Column
F Named Status) states "Complete" and then copied into another sheet titled
"Completed Tasks". (FYI: Column F consists of a drop down list: Complete,
Working, New, and Waiting on someone else.)

Thank you so much for your help!


"Gord Dibben" wrote:

"do(sic) not work" means what?

What do you want to happen and what is not happening?

The code was a suggestion only........you never did post any answers to my
questions in my first reply.


Gord

On Tue, 15 Jul 2008 12:43:00 -0700, Mindy
wrote:

Sorry, to bother you again! But I am using the below code and it do not
work. Can you see where I went wrong? Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed Tasks") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub

"Gord Dibben" wrote:

I explained all that in the reply to your other post but maybe you lost track of
that because it was not your original post.

Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Edit to suit..........target.cells.column = 6 means column F

You must have a sheet named Completed or edit that also in the code.

Alt + q to return to your Excel sheet.

Enter "complete"(no quotes) in a cell in column F and that row will be copied to
the Completed sheet at next available row.


Gord

On Wed, 11 Jun 2008 03:59:01 -0700, Mindy
wrote:

Yes, that is exactly what I wanted it to do. However, I never had to run a
macro in excel, and I am not sure how or what is the best way to do it. Can
you walk me thru some steps? Thanks a bunch!

"Gord Dibben" wrote:

One other thing I should mention.

You can hide the "Completed" sheet and it will still be updated.


Gord

On Tue, 10 Jun 2008 14:41:06 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

You want the column to hide itself or the row to hide itself?

I would say the row to be hidden and copied

For the row hiding see your other posting that you tacked onto someone else's
original.

To both copy the row to another sheet and hide the row use this code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub


Gord

On Tue, 10 Jun 2008 12:37:00 -0700, Mindy
wrote:

I have a main spreadsheet with 7 columns. (I have created a list to sort
through tasks, action officers, etc.) One of those columns is status. I
have put in drop down lists to pick the status ( complete, new, working..)
When Complete is selected I would like the column to hide itself and then
populated another sheet that has all the completed tasks(only) in it.

"Gord Dibben" wrote:

You mention a task sheet and a Main sheet.

"Pull" what to which "other sheet"

How will you know when the task is complete and which cells would be copied or
cut to the other sheet to be hidden?

Have you done this manually?

If so, maybe record a macro whilst doing it.

Or possibly event code to do it automatically when a certain cell turns to "task
complete".


Gord Dibben MS Excel MVP


On Tue, 10 Jun 2008 11:48:02 -0700, Mindy
wrote:

I have a task spreadsheet. When my task changes to complete I want to pull
to another sheet and hide it's view from the main sheet. Does anyone have
any suggestions?

Conditional formula/Please help before I throw my computer!
 

The code I provided will do that.

Hide the "completed" row in column F and copy that row to "Completed Tasks"
sheet at next available blank row.

Where are you placing the code?

It must go into Ongoing Tasks sheet module.

On one note.......saying you don't want to do it manually and record a macro
will not teach you anything and could jeopardize any further attempts from
other to assist.

If you want to email me your workbook...........change the AT and DOT to
punctuation............I will have a look.


Gord

On Fri, 25 Jul 2008 08:40:29 -0700, Mindy
wrote:

Redirecting any questions:
"Pull" what to which "other sheet"
How will you know when the task is complete and which cells would be copied
or
cut to the other sheet to be hidden?

I have a sheet titled ongoing tasks, which is populated with various tasks
and in Column F titled "Status" I have a drop down list created from a
validation range Q3:Q6). Once the task is complete and the Status has
changed in Column F titled "Complete" (by user selecting "Complete" from the
drop down list) I want the entire row to hide itself from the Ongoing Tasks
view and then autopopulate in a worksheet titled" Completed Tasks".

Have you done this manually?
If so, maybe record a macro whilst doing it.

No, I do not want to do this manually and record a macro.


Or possibly event code to do it automatically when a certain cell turns to
"task
complete".

Yes, I want to use a possibly event code to do this automatically. I
currently used the code provided below and it did not work. However, I did
not get an error message. Does anyone have any suggestions?


Gord Dibben MS Excel MVP


On Tue, 10 Jun 2008 11:48:02 -0700, Mindy
wrote:


"Gord Dibben" wrote:

See bottom of this post for my questions.

The code I posted will, if the selected cell in column F reads "completed",
copy the row to "Completed Tasks" sheet and hide the source row in source
sheet.

Sounds like what you need.

How doesn't it work?

You will have to have the Dropdown list in each cell of your usedrange in
column F


Gord

On Mon, 21 Jul 2008 07:21:00 -0700, Mindy
wrote:

Gord,

Thanks for catching my typo, but what I was trying to convey was that the
code provided (as a suggestion) did not work. I was hoping you could provide
your expert knowledge to further assist me. I am sorry, if I did not respond
to your reply, but I do not recall your question. If you kindly refrain your
question, and I will be glad to respond. Sorry for any confusion this may
have caused.

To be more specific on my challenge:

I would like to hide the row automatically when the task in Column F (Column
F Named Status) states "Complete" and then copied into another sheet titled
"Completed Tasks". (FYI: Column F consists of a drop down list: Complete,
Working, New, and Waiting on someone else.)

Thank you so much for your help!


"Gord Dibben" wrote:

"do(sic) not work" means what?

What do you want to happen and what is not happening?

The code was a suggestion only........you never did post any answers to my
questions in my first reply.


Gord

On Tue, 15 Jul 2008 12:43:00 -0700, Mindy
wrote:

Sorry, to bother you again! But I am using the below code and it do not
work. Can you see where I went wrong? Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed Tasks") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub

"Gord Dibben" wrote:

I explained all that in the reply to your other post but maybe you lost track of
that because it was not your original post.

Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Edit to suit..........target.cells.column = 6 means column F

You must have a sheet named Completed or edit that also in the code.

Alt + q to return to your Excel sheet.

Enter "complete"(no quotes) in a cell in column F and that row will be copied to
the Completed sheet at next available row.


Gord

On Wed, 11 Jun 2008 03:59:01 -0700, Mindy
wrote:

Yes, that is exactly what I wanted it to do. However, I never had to run a
macro in excel, and I am not sure how or what is the best way to do it. Can
you walk me thru some steps? Thanks a bunch!

"Gord Dibben" wrote:

One other thing I should mention.

You can hide the "Completed" sheet and it will still be updated.


Gord

On Tue, 10 Jun 2008 14:41:06 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

You want the column to hide itself or the row to hide itself?

I would say the row to be hidden and copied

For the row hiding see your other posting that you tacked onto someone else's
original.

To both copy the row to another sheet and hide the row use this code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub


Gord

On Tue, 10 Jun 2008 12:37:00 -0700, Mindy
wrote:

I have a main spreadsheet with 7 columns. (I have created a list to sort
through tasks, action officers, etc.) One of those columns is status. I
have put in drop down lists to pick the status ( complete, new, working..)
When Complete is selected I would like the column to hide itself and then
populated another sheet that has all the completed tasks(only) in it.

"Gord Dibben" wrote:

You mention a task sheet and a Main sheet.

"Pull" what to which "other sheet"

How will you know when the task is complete and which cells would be copied or
cut to the other sheet to be hidden?

Have you done this manually?

If so, maybe record a macro whilst doing it.

Or possibly event code to do it automatically when a certain cell turns to "task
complete".


Gord Dibben MS Excel MVP


On Tue, 10 Jun 2008 11:48:02 -0700, Mindy
wrote:

I have a task spreadsheet. When my task changes to complete I want to pull
to another sheet and hide it's view from the main sheet. Does anyone have
any suggestions?

Conditional formula/Please help before I throw my computer!
 

Gord,

I just wanted to show my appreciation! It worked great! Thanks again!

"Gord Dibben" wrote:

The code I provided will do that.

Hide the "completed" row in column F and copy that row to "Completed Tasks"
sheet at next available blank row.

Where are you placing the code?

It must go into Ongoing Tasks sheet module.

On one note.......saying you don't want to do it manually and record a macro
will not teach you anything and could jeopardize any further attempts from
other to assist.

If you want to email me your workbook...........change the AT and DOT to
punctuation............I will have a look.


Gord

On Fri, 25 Jul 2008 08:40:29 -0700, Mindy
wrote:

Redirecting any questions:
"Pull" what to which "other sheet"
How will you know when the task is complete and which cells would be copied
or
cut to the other sheet to be hidden?

I have a sheet titled ongoing tasks, which is populated with various tasks
and in Column F titled "Status" I have a drop down list created from a
validation range Q3:Q6). Once the task is complete and the Status has
changed in Column F titled "Complete" (by user selecting "Complete" from the
drop down list) I want the entire row to hide itself from the Ongoing Tasks
view and then autopopulate in a worksheet titled" Completed Tasks".

Have you done this manually?
If so, maybe record a macro whilst doing it.

No, I do not want to do this manually and record a macro.


Or possibly event code to do it automatically when a certain cell turns to
"task
complete".

Yes, I want to use a possibly event code to do this automatically. I
currently used the code provided below and it did not work. However, I did
not get an error message. Does anyone have any suggestions?


Gord Dibben MS Excel MVP


On Tue, 10 Jun 2008 11:48:02 -0700, Mindy
wrote:


"Gord Dibben" wrote:

See bottom of this post for my questions.

The code I posted will, if the selected cell in column F reads "completed",
copy the row to "Completed Tasks" sheet and hide the source row in source
sheet.

Sounds like what you need.

How doesn't it work?

You will have to have the Dropdown list in each cell of your usedrange in
column F


Gord

On Mon, 21 Jul 2008 07:21:00 -0700, Mindy
wrote:

Gord,

Thanks for catching my typo, but what I was trying to convey was that the
code provided (as a suggestion) did not work. I was hoping you could provide
your expert knowledge to further assist me. I am sorry, if I did not respond
to your reply, but I do not recall your question. If you kindly refrain your
question, and I will be glad to respond. Sorry for any confusion this may
have caused.

To be more specific on my challenge:

I would like to hide the row automatically when the task in Column F (Column
F Named Status) states "Complete" and then copied into another sheet titled
"Completed Tasks". (FYI: Column F consists of a drop down list: Complete,
Working, New, and Waiting on someone else.)

Thank you so much for your help!


"Gord Dibben" wrote:

"do(sic) not work" means what?

What do you want to happen and what is not happening?

The code was a suggestion only........you never did post any answers to my
questions in my first reply.


Gord

On Tue, 15 Jul 2008 12:43:00 -0700, Mindy
wrote:

Sorry, to bother you again! But I am using the below code and it do not
work. Can you see where I went wrong? Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed Tasks") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub

"Gord Dibben" wrote:

I explained all that in the reply to your other post but maybe you lost track of
that because it was not your original post.

Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Edit to suit..........target.cells.column = 6 means column F

You must have a sheet named Completed or edit that also in the code.

Alt + q to return to your Excel sheet.

Enter "complete"(no quotes) in a cell in column F and that row will be copied to
the Completed sheet at next available row.


Gord

On Wed, 11 Jun 2008 03:59:01 -0700, Mindy
wrote:

Yes, that is exactly what I wanted it to do. However, I never had to run a
macro in excel, and I am not sure how or what is the best way to do it. Can
you walk me thru some steps? Thanks a bunch!

"Gord Dibben" wrote:

One other thing I should mention.

You can hide the "Completed" sheet and it will still be updated.


Gord

On Tue, 10 Jun 2008 14:41:06 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

You want the column to hide itself or the row to hide itself?

I would say the row to be hidden and copied

For the row hiding see your other posting that you tacked onto someone else's
original.

To both copy the row to another sheet and hide the row use this code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 And _
Target.Value = "complete" Then
With Target.EntireRow
.Copy Destination:=Sheets("Completed") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
.Hidden = True
End With
End If
enditall:
Application.EnableEvents = True
End Sub


Gord

On Tue, 10 Jun 2008 12:37:00 -0700, Mindy
wrote:

I have a main spreadsheet with 7 columns. (I have created a list to sort
through tasks, action officers, etc.) One of those columns is status. I
have put in drop down lists to pick the status ( complete, new, working..)
When Complete is selected I would like the column to hide itself and then
populated another sheet that has all the completed tasks(only) in it.

"Gord Dibben" wrote:

You mention a task sheet and a Main sheet.

"Pull" what to which "other sheet"

How will you know when the task is complete and which cells would be copied or
cut to the other sheet to be hidden?

Have you done this manually?

If so, maybe record a macro whilst doing it.

Or possibly event code to do it automatically when a certain cell turns to "task
complete".


Gord Dibben MS Excel MVP


On Tue, 10 Jun 2008 11:48:02 -0700, Mindy
wrote:

I have a task spreadsheet. When my task changes to complete I want to pull
to another sheet and hide it's view from the main sheet. Does anyone have
any suggestions?