Mark employee absent and they populate on the missed shift tab

New Contributor

Hi, guys. Does anyone have a formula or advice on achieving this in my workbook? I have a tab that houses the day of the week for all seven days of the week. When we mark a person absent for a particular day, that specific line, including the start and end time, populate on the missed shift-tab. 


Here is an example of how each day of the week looks. I have blocked out sensitive info, but here's an idea of what we are working with. Right now, we are copying and pasting, but I would love to move them over automatically. 



Here is an example of the missed shift tab 




2 Replies

Hi @Managerneedhelp24 


I would propose to use Power Query for this. Here are the general steps:

1. convert all the weekdays lists in a separate formatted table (menu "Home | Format as table")

2. load all these tables into Power Query (menu "Data | From within Sheet")

3. Within Power Query editor, append all 7 weekdays queries into one (menu "Home | Append Queries)

4. Put a filter on the attendance column and filter only the absent ones

5. Close & load to your absence sheet


Once that's done, you can update the absence list after any changes easily with a right-click and "Refresh"

Use the FILTER function.