A (non Volatile) way to do this without using INDIRECT?

Hi all, I have included a little graphic explanation below as well as a sample wbk... What i currently have works, but the actual data sets are much larger and manipulating them is becoming quite laggy so after doing some google research, i discovered it might be due to the Volatile formulas that are using INDIRECT in the Summary table headers.

Basically what's going on is the Details table on the Details ws is a very large table. The user filters this table down to the smaller set of relevant records they want and then we want to display certain values (or columns) from that result set in a smaller summary table on the Summary tab.

The Summary table column headers use data validation to reference a series of column names from the Details table so the user can select which columns they want to display the data for. The formula in the table uses the INDIRECT function to apply the user's column selection in the INDEX function for lookup in the Details Table; so the first part of the formula is =INDEX(INDIRECT("DetailsTable["&C4 (where C4 is the Summary table column header containing the data validation list)&"]")... then it goes into the MATCH part which uses the Counter column of the Details table to reference the correct row... this is to reference only the visible rows in the filtered Details Table...




So as I said, this currently works as expected (I think), but it's getting a bit slow and i'm guessing that recalculating the Volatile formulas is part of the problem?

So I'm wondering if anyone out there knows of a different, better, faster, more efficient way to accomplish this?

Thanks,
Joe

View attachment sample dynamic columns.xlsx

Hi,

a possible approach in b5 to be copied across


=IFERROR(INDEX(Detail!$A$2:$G$10,MATCH(ROWS($1:1),Detail!$G$2:$G$10,0),MATCH(B$4,Detail!$A$1:$G$1,0)),"")

Regards