HOW TO… Excel: A Truly Non-Volatile Solution for Dynamic Data Tables

This is a follow-up to the last tutorial. Thanks to reddit user /u/small_trunks for noticing my blunder!

Unfortunately, ‘SUMIFS’ couldn’t be used on its own to create the dynamic data tables. But don’t fret! I have another solution up my sleeve that doesn’t rely on volatile functions like ‘INDIRECT’ and ‘OFFSET’! It relies entirely on ‘INDEX’ and ‘MATCH’.

We’ll need to add an additional list next to our measure names and back-sheet names lists on our output sheet, which I’ve named the ‘Master Sheet Start Point’ column. In this column we can use the ‘MATCH’ function to work out the first iteration of each of the measure names in our backsheet. The formula in this column is:

=MATCH($A16,MasterBacksheet!$C$2:$C$31,0)

Then, below our two cells that work out which sheet name and measure name (H16 and H17) is corresponding to what has been selected by the combo box we can add another cell that will find the starting point of the selected measure in the master back-sheet. The formula will therefore be:

=INDEX($E$16:$E$18,$G$16)-1

We need to include the ‘-1’ as we’ll also be using ‘MATCH’ in the main formula for finding the row number of each organisation so we don’t want to ‘double count’.

NewSolution

The formula we’ll be using in the dynamic data table will be an ‘INDEX’, ‘MATCH’ formula as follows:

=INDEX(MasterBacksheet!$D$2:$AA$31,MATCH(‘Output Sheet’!$B4,MasterBacksheet!$B$2:$B$11,0)+’Output Sheet’!$H$18,MATCH(‘Output Sheet’!C$3,MasterBacksheet!$D$1:$AA$1,0))

Breaking this down gives us:

  1. =INDEX(Full Array of Master Back-sheet Data,
  2. MATCH(Org name, Org Name list in Master Back-sheet, Look for an exact match) + Measure’s Master Sheet Starting Point,
  3. MATCH(Date, Date Headings in Master Back-sheet, Look for an exact match))

And then this formula can be copied across the entire data table! With that we’ve solved the issue, we have a non-volatile method of creating a dynamic data table.

NewSolution2.PNG

Once again if anyone has any other methods of solving this solution please share in the comment section below – I’d love to see what creative methods the community can come up with!

The workbook can be downloaded here.