Mar 10 2022 06:27 PM
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
Mar 10 2022 10:55 PM
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"
Mar 11 2022 07:03 AM