Results 1 to 2 of 2

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

  1. #1
    Seeker joe1250's Avatar
    Join Date
    Feb 2018
    Posts
    14
    Articles
    0
    Excel Version
    2016

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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...


    Click image for larger version. 

Name:	sample graphic.jpg 
Views:	26 
Size:	93.5 KB 
ID:	8680

    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

    sample dynamic columns.xlsx

  2. #2
    Acolyte Canapone's Avatar
    Join Date
    Oct 2011
    Location
    Italy
    Posts
    99
    Articles
    0
    Excel Version
    Excel 2010
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •