Oct 06 2021 02:53 AM
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?