To-Do List: How to populate a sheet if drop-down criteria is met in multiple other sheets

this routine will render your desired results...

Assuming that you are new to VBA (visual basic macros) to run the routine :

1. goto to the view tab
2. select the macro
3. click run

Sub urgent()
Set sh1 = Sheets("Urgent")
r2 = 3
For s = 2 To Sheets.Count
r = 3
Set sh2 = Sheets(s)
While sh2.Cells(r, 2) <> ""
If Trim(sh2.Cells(r, 3)) = "Urgent" Then
sh1.Cells(r2, 2) = sh2.Name
sh1.Cells(r2, 3) = sh2.Cells(r, 2)
r2 = r2 + 1
End If
r = r + 1
Wend
Next s
End Sub