Subtotaling Next to Pivot Table

Brass Contributor

I have an output in a pivot table and wish to use this for forecasting future month based not the historical actuals from the pivot table. 

 

I believe I may have my solution but want to see if there's a more efficient way to do this. 

 

I roll forward the months (R5:Z5), create a unique lookup ID with a concatenation of Account # and Text (column K), and define in a different table (H2:I6).

 

I use a XLOOKUP on the lookup to the table and add each value to get the correct value and it works but I must repeat each yellow subtotal line. This works as I just need to make one XLOOKUP and roll over the next month's. Not a big deal. I checked and it works.

 

I do this over 450 rows and want this as new lines can be added and a simple SUM can be distorted over and then I'd have to manually fix each row due to the shift. Mapping in the table in columns H:I helps to dump in any new accounts that will appear as the year goes on. 

 

I fear so many XLOOKUPs will increase size and processing in the file. Is there an easier way to handle this for each yellow line?

 

Screen Shot 2021-10-06 at 11.40.31 AM.png

0 Replies