My advice is - start with redesigning it!

With current design, when you get a new location, agent, or type of pie, you have to add a new sheet into workbook, or redesign all location sheets, and then redesign the sheet Data - and all this manually.

Instead, use a single sheet for data entry, with columns like
Year, Location, Agent, Pie

Then you can easily design 2 report sheets, where on one user can select a year a client, and get a list like on nay of your current location sheets, and on another the user can select a year, and got statistics for all Agent for this yer (like your Data sheet). This is all you need to get exactly the same functionality as with current design, an with a lot of less haggle!

To get all this more foolproof, you also need sheets, where user can register locations, agents, pies, and optionally a sheet where a list of years which is calculated from some start year up to current one, or the year of workbook is registered. Those registries are used as sources of Data Validation lists in data entry table, and for selecting report conditions. Using data validation also ensures, that user can't easily make any typos when entering new data into data entry table.

With such design:
Whenever a new location, agent or pie is added, all what is needed is register it. After that, user can enter new data using this new value, and it is displayed in reports (assumed the report sheets are designed correctly);
User can use this workbook for several years (in case you chose the option with year list calculated instead of fixed year number.
With fixed year number option selected, every new year user creates a new copy of workbook, changes the year number, and clears the entry table leaving at least a single empty data row there (to keep data validations). And this is all - user can start to enter new year's data,