In series drop down filters from multiple sheets

Portion rant and question. Unfortunately, being able to create pivot tables has earned me the title of "Excel Wizard" in my office, and I have been tasked with creating a dashboard to pull filtered data from several sheets easily. The database I am pulling from is fairly large, and outside of my abilities, YouTube and online searches are not getting me the exact answer I need.

In theory, the end result will be a dashboard with two dropdown filters. The first is to select the specific location (37 total), and the second is to select information from 10 separate sheets, like contact information, contract expirations, insurance policy information, equipment information, etc. They would also like each category to have its own sheet so the information can be looked at as a whole. I have pushed to have seperate excel files for each location with the information needed, but they want one place to view and edit all of the data.

The other caveat to this is that since pivot tables are a mind-boggling creation, I fear any complex formulas or functions may get damaged as they try to edit/update information in the data sheets.

My initial thought was to consolidate all of the information onto one sheet, but the different headers/information types stopped that plan quickly.

Besides advocating more for some type of software to store this information and accomplish this "dashboard" need, is there a solution to my problem?