my best idea is that you should create 3 different modules with different dimensions, you can let people input data on different levels and summarize the input from other modules. Something like the simplified example below:
Your challenge here is that the module is dimensioned at the lowest level in the hierarchy and you are allowing users to enter data at higher levels.
You need to bring all the inputs in line with the same dimensionality.
Create a systems module for the lowest level and a series of line items formatted by the parent, grand parent and great grand parent lists. Using the PARENT function populate each line item with the corresponding list items.
In your target module create a line item at the P5 dimension for all other P levels. For example, a line item for P3 dimensioned at P5 and a separate line item for P4 dimensioned at P5.
For each of these reference the line items P5 and LOOKUP the P5 line item in the new system module. Do the same for P4.
Add these new line items into the Final line item.
Chris Heathcote Bedford Consulting
Gold Partner and Regional Partner of the Year 2021, EMEA