Generating a Dynamic Excel Template

I am tasked with creating an Excel template that mainly validates the calculations in a source file. I have limited knowledge of Excel functions/formulas and would like to seek assistance in generating this template.

The template will pull in the source file data and loaded in a worksheet. The source data has columns as input values to metric columns in calculation worksheet and other columns calculated from these input value columns. There will be a calculation difference worksheet which compares the calculation worksheet with the source worksheet. If there is a difference, the value will be highlighted.

I have a sample file (not a template yet) of how it should look like. The Calculation and CalcDifferences worksheet should dynamically populate the categories from the source and matched against the Categories worksheet. The metric columns to be calculated are populated in the Calculation worksheet. There is a formula worksheet for reference on formulas for the input and calculated columns. Every calculation should roll up to the top level categories.

A user should be able to apply the calculations and differences to one or multi level categories. For example, a user can choose to calculate for one level category, A01, and later on change it to another level category, A02 or even choose one or more level categories to calculate.

I have used IF function for the comparisons. I have read a little bit about macros but not sure if applicable in this case. Is this the correct approach?

In summary, this template should be able to get a data source file and validate its metric data by calculating metrics using input values and formulas and comparing source metric data with calculated metric data. Any help to generate this template is greatly appreciated.