Spreadsheet 2 is used to project the budget for the specified fiscal year. Spreadsheet 1, on the other hand, will be used to brief the CEO how each department plans on spending money and how their exercises align with the strategic goals.
Spreadsheet 1 is built as swim lane with each department (BN) owning a lane (row) across time (columns). This format will allow the CEO to see how the strategic goals are operationalized with the budget. The dates shown are granular to the month and allows six entries per BN. Each entry is organized by event and receives inputs for “Event”, “PAX” for number of personnel, “Days” for number of days per Event, and “Cost” for overall event cost projection.
Spreadsheet 2 has a more traditional array. Each column represents the data category to be filled like event name, date, cost, etc. while each row represents a single case.
Spreadsheet 2 is the standing tool that's been used to project budget across our organization, but the Chief of Staff wants to use Spreadsheet 1 to brief the CEO. Since the data used in both spreadsheets are the same, I want to link or auto-populate data on spreadsheet 1 from the data that exists in spreadsheet 2.
This is being done on .xlsx format but am unable to upload them here. Can't find a format this thread will accept, so here are some images. Link to sample spreadsheets: https://drive.google.com/drive/folders/1u2v78Iz7E9G-aZVDceR2D1rWPV0dYka5?usp=sharing