Tool Tracker

[underdawg83]

I have been tasked to update our tool tracker. We use this tracker to keep track of all tools and equipment that need to be calibrated. However, I am running into some issues that I cannot get through. The data I am trying to measure is using dates. We are trying to see how many items are due in the month that we select versus how many have been sent out. I have used pivot tables to consolidate my due dates and my out dates. However, my averages are just not coming out right, at least I don't think they are.

I have created a "status report" that updates automatically based off the month that is selected. I am using data validation for the list of the months.

I have attached some snap shots for some clarity.

The picture with the percentages, I am using (=SUMIF($O$4:$O$28,B3,$Q$4:$Q$28) to calculated how many tools that are due in a month. I don't think that these numbers should change, at least not by much because what is due in Jan this year will be due in Jan next year. However, when I try to compare the bottom table, these numbers should change based off the year as we update our tracker.

I then use (=F20/F3) to figure out the percentage of us sending out that month. Then the "annual goal" is just the average of the monthly goal.

Any help will be very much appreciated.