Forum Discussion
use results from filtered table
Hi,
I'm using a spreadsheet to monitor staff clock in and out hours across various properties. We use Bright HR, which allows us to export a csv for each week, from which I copy and paste the columns showing name, date, property where they were working, and the amount of hours for each week.
I have set the table up so I can filter via name. This will allow me to see each caretaker's hours worked. I then need to be able to see if their total cumulative hours are above or below the set amount of hours we allocated for that property.
For example, If Duane works at property A, and Property A has 20 hours a week allocated to it, and Duane has done a cumulative 48 hours for that month, then I need to be able to see how many hours Duane is short, and how many hours he owes us.
This needs to be cumulative across the year, so I can just add the hours each week to my base table, and have a different sheet display the amount of hours along with the caretaker's name, amount of hours done, how many hours should have been done, and any difference in those totals.
Any help would be appreciated!
operations240 in the attached are 2 examples 1 pivot table and 1 example manual FILTER tables.
Since each of these are dynamic you really need to have the allocated hours in a table for lookup. In the attached I just added a column to the existing table for allocated hours. In the pivot table I added a calculated field for the difference in the manual I just did more calculations (lol). Hope this helps
4 Replies
- If you could provide a sample sheet (no personal info) we could help more but in general you probably can do this with a pivot table or manually using FILTER(). I would recommend you 'format as table' (see home tab) to more easily refer to the columns of data. If I assume you do that and then click on the 'Table' menu and name that table 'Data' then something like:
=SUM(FILTER(Data[Hours], (Data[Name]="Duane")*(Data[property]="A"),0))
to get sum of hours for Duane for property A but I would replace both "Duane" and "A" with references. For example in sheet 2 you can have cell A2 be =SORT(UNIQUE(Data[Name])) to get a full list of names from the other table and then in B1 be =TRANSPOSE(SORT(UNIQUE(Data[Property]))) to get a full list of properties in row 1
then in B2 you would have:
=SUM(FILTER(Data[Hours], (Data[Name]=A2#)*(Data[property]=B1#),0))
but that is just a manual reproduction of a Pivot table but if you know how to do that you could tweak it for more customized calculations/manipulations if needed (e.g. incorporating the max hours per location and such but not sure how that all plays in). But if not need I would just do the pivot table and you're done.m_tarler I tried a pivot table prior to making this post, but I am very rusty (haven't done a pivot table for many years!), and couldn't seem to get the hours to add up correctly no matter how I changed the output in the options.
This is a shot of the table I am using.
I want to be able to have an output that looks something like this:
I want to be able to filter the first table by caretaker name, and have the filtered cells be the output for the second table. The hours allocated to property will be populated manually, with the difference a simple calculation.
operations240 in the attached are 2 examples 1 pivot table and 1 example manual FILTER tables.
Since each of these are dynamic you really need to have the allocated hours in a table for lookup. In the attached I just added a column to the existing table for allocated hours. In the pivot table I added a calculated field for the difference in the manual I just did more calculations (lol). Hope this helps
