Program Management Workload Data spreadsheet with Pivot Tables for reporting

version: Excel 2016

Hello!

I created a workload database in excel to track Program Consultant Activities within my organization. The Top Level sheet consists of seven pivot tables that get their information from different sheets/columns. The Current program pivot table pulls its data from the sheet named “Active”, the CY 2019 and Level PTs pull from “CY19”, the CY18 and Level PTs pull from “CY18”, the 2018 – 2019 PT pulls from “Additional Functions”

The sheet named “ALL” is where I put all programs, active, 2018 or 2019 and from there I sorted, filtered, and copied the information to the corresponding sheet so that I could create their respective PivotTables.

The Program Support sheet includes all the information in “ALL”, “Active”, CY19”, and “CY18” with the addition of the activities the consultant/teams are performing for each specific program. Some programs have multiple activities from one consultant/team or some programs have multiple activities from multiple consultants/teams.

In order to create the Top Level sheet it required a lot of sorting, filtering, and manual entry on my part but now I’m handing the spreadsheet off to someone else and I would like to simplify the process. I have another spreadsheet where each consultant inputs their program information under their own sheet, then I pull that information and put it in the Program Support sheet of this spreadsheet. My boss wants to be able to have numbers for anything and everything recorded in this workbook. “How many active programs are there now?” “How many programs were active in 2018?” “How many programs did each advisor/team work on in 2019, including programs that are now inactive?” Plus any other combination of data/information you can think of.

My biggest headscratcher for this is definitely the Program Support sheet due to their being multiple activities per program, plus duplicated programs (which is why some of the Dept names are in parentheses). I need an easy way for the consultants to update this spreadsheet and be able to pull the information. I removed a majority of the programs but wanted to leave enough for a snapshot of what I’m working with. Any help or guidance would be greatly appreciated.

Thank you for the quick response.

I started to build an Access Databse but I had take a class online in order to really understand what I'm doing with Access and I'm only about halfway through the class.
I only have about 2 weeks until I hand off the spreadsheet (or whatever I have) to the next person and I just don't think that's enough time for me to finish the Access Database
and write "how to" guides for those filling it out and those pulling queries.

I took a look at the "Understanding Star Schemas" link and it looks like it's similar to what I was attempting in Access with relationships, tables, queries, etc. before going
back to my excel spreadsheet. I know enough about Access to build a database that doesn't really work the way it's supposed to.

My reasoning behind having separate columns for CY18 and CY19 is because I had to tell my boss how many programs were active in CY18, how many programs were active in CY19, and
how many programs are currently active. Some programs were active in CY18 and CY19 but aren't currently active, some programs were active in CY18 and CY19 and are currently active.
So if I can still pull that information by making CY18/CY19 one column, then I'm just not sure how to accomplish that.