Dynamic formula range for SORTBY and INDEX/XMATCH into a pivot table

Hi everyone,

I have a problem when it comes to automatically updating an excel report using SORTBY dynamic ranges (attached excel).

There is a pivot table which everytime there is an update, a new column will be added for a new year (e.g. next year will be 2022 on column Q).

Below the pivot I have a clean report which uses INDEX/XMATCH to retrieve the values for "Other sources" which I always want to keep on top of the clean report, and below the "INDEX/XMATCH" formula I have a SORTBY that retrieves all values and sorts them by the LATEST YEAR (currently 2021).

My desired outcome is to not have to drag the INDEX/XMATCH formula to the next column, and to not have to update the formula range inside the SORTBY cell once a new year is added (basically I would need to expand the SORTBY range and also specify that the sort should be based on 2022 column, not 2021).

Could someone kindly walk me through how I can achieve this? I have 0 clue where to even begin...

Many thanks!
B

How about

Formula:

Please Login or Register  to view this content.

I would have done that, but I created a lot of (required) reclassification columns in power query/pivot in order to arrive at a column needed => which was in turn used in the pivot.

My base dataset is quite raw and would not allow a direct reference to it, but good idea for another case!

Originally Posted by Fluff13

How about

Formula:

Please Login or Register  to view this content.

Superb job, that worked! I will analyze the formula to figure out exactly how you did it because I am not familiar with LET, so that I can replicate it into the future.

Many thanks!!

Glad to help & thanks for the feedback.