Conditional formula

  • Thread starter Thread starter Mindy
  • Start date Start date
M

Mindy

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?
 
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
 
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.
 
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
 
One other thing I should mention.

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


Gord
 
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!
 
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
 
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
 
"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
 
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!
 
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
 
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
 
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
 
Back
Top