I want to create a to-do list on a new worksheet that pulls tasks from another worksheet if certain criteria is met. I'd really this to be a macro that updates automatically when the criteria changes since the users are very very unskilled in Excel. I want them to open the file and see the to-do list (I'll have that sheet as sheet 1 so it's the first one they see).
In column A on worksheet 1 (Dispatch Items) I have a list of tasks
In column I on worksheet 1 (Dispatch Items) , I have a list of values 1 or 0 to represent whether the task has been done
I would like the list of tasks in Column A to be displayed on worksheet 2 (To-do) in column A if the value in I (sheet 1) is a 1.
The main list is currently 300 records long, but that may change, so it needs to be dynamic.
•I have no clue where to start. I tried vlookup, but that returns numbers, not text.
•I tried pulling the whole list onto the To-Do sheet and then found a macro to hide the rows that have a 0 in column I. That sorta worked. But, I don't know code and can't figure out how to modify it so that the macro updates when the criteria on Dispatch Items (sheet 1) changes.
What do you think? Any ideas? Thank you for your time.
Create a to-do list on sheet 2 based on criteria in sheet 1
-
- 3StarLounger
- Posts: 310
- Joined: 19 Apr 2010, 16:18
- Location: middle of the state of Washington
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Create a to-do list on sheet 2 based on criteria in sheet 1
You could use Advanced Filter in VBA; the code needs to go into the worksheet module of the Dispatch Items sheet.
The code uses M1:M2 as criteria range; you can change this in the worksheet and in the code.
See the attached sample workbook.
The code uses M1:M2 as criteria range; you can change this in the worksheet and in the code.
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Dim m As Long
If Not Intersect(Range("A:A,I:I"), Target) Is Nothing Then
m = Range("I" & Rows.Count).End(xlUp).Row
Range("A1:I" & m).AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("M1:M2"), _
CopyToRange:=Worksheets("To-do").Range("A1"), _
Unique:=False
End If
End Sub
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans