I have a table named Hours with three columns; Cost Code, Description, Allowance.
I have another table named Tracked Hours on a different sheet that has four columns; Employee (drop down using data validation), Cost Code (list from the Hours table), Hours and Date.
I am using these tables to keep track of our employees hours against our budgeted hours for a project.
I created a pivot table to summarize the tracked hours that shows how many hours each employee has used from each cost code. I would like to add the allowance column from the Hours table and show a total hours remaining column in my pivot table but I cannot figure out how to incorporate it in my pivot table.
I would like my pivot table to show a row for each cost code, with rows for each employee and how many hours they've used from the total allowance. Then I would like a total row or column that shows how many hours are left from my allowance. I am open to suggestions or a better way of presenting this data.
Please advise.
I have attached a sample spreadsheet of what I have done.
