Create a to-do list on sheet 2 based on criteria in sheet 1

[MelanieB]

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.

[HansV]

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.

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

See the attached sample workbook.

FilterSample.xls