Continuously Updating Pivot Table that also requires ranking.

Good Morning,

I have a Pivot Table that I'm trying to build for my employer and I'm having a hell of a time making it do what I need it to do; mostly because I'm just way too new to Pivot Tables and Power Pivot. I'm working with Excel 2016 btw.

I've attached the document and sanitized it as much as I could without breaking it.


Here is a summary of what is and what needs to happen:

1. The Pivot Table pulls from an external source for it's data.
2. The Pivot Table is constantly being updated with new entries as they are added to the external data source; so the spacing, rows and numbers are constantly changing. The columns will remain the same as what is shown.
3. The Pivot Table layout is what senior management wants (or very similar).
4. They also want the ranking (top 3 as noted) for each Trade as it is currently set-up. The ranking is determined by the number and severity of the injury. Report Only entries are the lowest severity and Lost Time are the highest severity. If there is a tie in total numbers for a Contact Type, then the ranking is determined by the entry with the highest number of severity. (Starting to get a headache trying to explain this).

PROBLEM:

1. I cannot get the layout to "advance" when the new rows with updated data are created. It messes up the layout (see point 3 below).
2. I cannot get the ranking system to work at all. Currently I am doing this manually, but it would be awesome if Excel could do this automatically.
3. Also, because the Ranking System layout is separate from the Pivot Table layout, when the Pivot Table Data updates then the Ranking System layout is askew if the Pivot Table has added a new row or 10. Then I have to manually redo the Ranking System layout again to match the Pivot Table Layout. (I got a bigger headache just typing that).

I apologize if this is an easy fix and I'm just dumb and blind. There will be UBER MAJOR karma points (maybe even some cash?) to anyone able to help me fix this and make it work.

-THANKS!!