Requesting assistance with dynamic list with 2 way lookup

Thank you again for the response.

I will have to wrap my head around all of this but so far it seems to work.

Possibly some follow-up questions to come.

I had a solution but then I found a better one and then I found an ever better one and then I found an even better best one. :-)

I works like this:

Copy Sheet1 (3) to Sheet1 (4).

Concatenate the three header rows in row 7.

Select A7:R17 and format as a table (CTRL-T). Give the table a name: tbl_Crosstabular.

Import the table to the query editor and transform it. See the steps in the query editor.

Give the query a name: qry_Unpivoted_table

Load the query as a connection only.

Back to the query editor and create a reference of the query and pivot it. See the steps in the query editor.

Give the query a name: qry_Pivoted_Table

Load the query into a new worksheet.

Copy the table to another new worksheet as values only.

Select the header row and the data rows and format as a table (CTRL-T). Give the table a name: tbl_Basedata.

Add the columns Difference Revenue and Difference Profit with the respective formulas.

This will be your new base data list. Add new data, change and edit data only in this list.

Delete every previous table and query. The are not needed anymore.

And now for the finish:

Insert a pivot table:

Divison, Month, Lead, On/Off, Difference Revenue and Difference Profit as a report filter. Alternatively insert slicers for each of the fields.

Apps in the rows area.

Actual Profit and Actual Revenue in the values area.

Bonus:

Load tbl_Basedata to the query editor and transform it. See the steps in the query editor.

Give the query a name: qry_Unpivoted_Basedata.

Load the query as a pivot table and add it to the data modell.

Month and Type in the columns area.

Division and Apps in the rows area.

Rightclick on the query name in the pivot table field list -> Insert measure.

Measure name: Value2.

Meaure formula:

=CONCATENATEX(qry_Unpivoted_Basedata,qry_Unpivoted_Basedata[Value],"")

Click on OK.

Add the measure to the values area.

Remove the grand totals.

I suppose, since my query is getting a bit complex, right?

However, I am not sure how power query works.

There may be a learning curve for that.

For the question at hand can you advise?

I will look into learning more about power query.

I think it's time for a complete change. No more cross tabular model but instead a record list model.

The use of Power Query is advised.

Would you agree?

Thanks again for being so responsive.

This new formula does get the desired output; but when the month of interest changes I would want to reference columns q and r, instead of the static reference to i and j.  How can I achieve that?