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

Hello everyone! I am new to this forum. I have a new job and I am trying to learn Excel - please bear with me as I am a little numerically challenged but am so keen to learn.

I have created a spreadsheet (attached) to try to work as a basic To Do List.

I would love to be able to populate the various sheets (Ops, HR etc) with tasks that pertain to that area of my job, and then have those I have marked as 'Urgent' populate into the first sheet.

Is this even possible? I have spent an hour trying to work this out based on LOOKUP, and I just can't seem to get the hang of it. Sorry if this is a really basic thing I am missing.

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

You are welcome!!!. Glad I could help. Post any of your needs anytime.