Pivot table question

[Davebro]

I have a pivotable table which Columns A,B,C which are from the data set, B2 and B3 are the highest number from Columns B & C. Column D has the calculation =(B5-B$2)-(C5-C$2) and column E ranks them, In H5 I am using =CHOOSECOLS(SORT(FILTER(A5:G13,E5:E13<=3),5,TRUE),5,1) formula (which works well and was given to me by a forum contributor), however when I use the slicer to move to another time and the length of the columns move from say 13 to 6 or 13 to 16. is there anyway that the calculations can adjust to the altered column length.

 

[arun_eagertolearn]

Hi

Please check whether finding the last row using ROW&OFFSET, and then using that in INDIRECT helps

Finding last row (13 in the image pasted) in cell H1 =ROW(OFFSET(A4,COUNTA(A6:A10),0))-1

Please use indirect as below (replace G13 with INDIRECT("G"&H1), E13 with INDIRECT("E"&H1) etc)

=CHOOSECOLS(SORT(FILTER(A5:INDIRECT("G"&H1),E5:INDIRECT("E"&H1)<=3),5,TRUE),5,1)

 

[Davebro]

Hi

Please check whether finding the last row using ROW&OFFSET, and then using that in INDIRECT helps

Finding last row (13 in the image pasted) in cell H1 =ROW(OFFSET(A4,COUNTA(A6:A10),0))-1

Please use indirect as below (replace G13 with INDIRECT("G"&H1), E13 with INDIRECT("E"&H1) etc)

=CHOOSECOLS(SORT(FILTER(A5:INDIRECT("G"&H1),E5:INDIRECT("E"&H1)<=3),5,TRUE),5,1)

Wow, thank you, much appreciated.

 

[arun_eagertolearn]

Thanks for your feedback