Macro to add sum total to track percentages

spenserallen7

New Member
Joined
Mar 13, 2024
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Screenshot (81).png

I have attached an image of the file I am working with for my fire department, but I have removed all names to protect privacy. I am attempting to track the amount of time people spend on the ambulance vs fire truck and the amount of time people get to ride out of rank (qualified to ride LT or Driver but aren't in that position but fill the role as needed due to vacation time and sick time). I want to make sure everyone is being treated equally.

First, what I would like to do is for the top 2 charts on the right to track Fire fighter and paramedics specifically and how often they ride on the unit vs fire truck. So I need the chart on the right, to find the individuals name on the left under "daily assignment" "personnel" and then move over to the Unit Categories (Unit 1, Unit 2, Unit 3, Unit 4, and Unit 5). Once there, slide over to make sure that the "Code", "HRS" and "TO" columns are blank. If so I want it to add a value of 1 to the "Unit 2" column on the right hand tables for that respective person. Additionally, add 1 to the "Total" Column on the right hand tables too so that I can track it throughout the year and the use the first "Unit" column as a percentage.

I also want to do the same for "Fire" on the right hand side columns. I want it to search for that specific persons name across Truck 1, Engine 2, etc. and then reference the same columns as above to be blank before adding a value of 1 to the right hand table columns "Fire 2" and "Total".

If it is easier, it can search the whole table for both of those formulas/macros. The individuals name could be under Personnel twice, but there will only be one spot where their values are BLANK for "Code, HRS, and TO".


Finally, I want it do the same thing for the table on the right hand side that says "out of rank time". I want it to search that persons name on the left hand side table until it finds their name and then moves to the far right under the column "Comments" and if it finds "AL24", "AL12", "AD24" or "AD12" it returns and adds a value of 1 to the right hand table under "AL" or "AD" to keep a running log.

Is this even possible? It sounds complicated to me, but I need it to be as simple as possible as the person who will be using this is not very technological. It would be great if this was some form of a macro that I could add to a "click me" button for him each day.

Let me know your thoughts and if you need any more info.

EDIT: I went ahead and added a 2nd picture to show how some names (letters in this case) are repeated but only 1 of them is blank in the cells referenced above as that person has been moved from their original apparatus of the day to a new one to cover sick/vacation and that new apparatus they are ACTUALLY riding on is the one we want to track.
Screenshot (82).png
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top