This is a continuation of the previous tutorial.
Due to the volatility of the INDIRECT function, it may be necessary to avoid using it when trying to create large dashboards which will be handling and outputting large amounts of data. So, we’ll need an alternative in these cases. One of the ways this can be done is by using the SUMIFS function, and structuring our data to be compatible with the SUMIFS function.
This has been achieved using SUMIFS, but OFFSET (another volatile function) has been used to cover one of SUMIFS weaknesses. A completely non-volatile method can be found here.
This will be done on the worksheet from the previous tutorial. A download link is available at the bottom of that page so you can follow along.
First, we’ll need to create a new ‘master’ data back-sheet that compiles all the data back-sheets to allow SUMIFS to work correctly.
A new column called ‘Measure’ has been added to the old structure which contains the measure names, and the data has been pulled from the other back-sheets by simple cell references.
Now we need to change the formula in the output sheet’s data table to use SUMIFS. But before that, we’ll need add a new INDEX that checks the Measure Name selected by the combo box. H16 on the output sheet can be copied down to H17 and then have its INDEX array changed to the measure name list as opposed to the back-sheet name list so the new formula in H17 is:
We can now change the dynamic data table’s formula. The formula used in the top left (C4) of the table is as follows:
=SUMIFS(MasterBacksheet!D$2:D$31,MasterBacksheet!$C$2:$C$31,’Output Sheet’!$H$17,MasterBacksheet!$B$2:$B$31,’Output Sheet’!$B4)
If we break this down we get:
- =SUMIFS(SUM_Range , Criteria_Range1 , Criteria1, Criteria_Range2, Criteria 2)
- =SUMIFS(Apr-15 Column (frozen to row numbers using absolute referencing, $), Measure Column, Selected Measure, Org Name Column, Org Name (frozen to column number using absolute referencing, $)
A weakness of this formula as it stands is the way we’ve referenced the SUM range, using direct references. To allow this formula to be more flexible we can instead use another function to work out the SUM range instead of directly referencing it. One way to do this is to use the OFFSET function. The way to construct the OFFSET function within this formula to correctly determine the SUM range dynamically is as follows:
As pointed out by reddit user /u/small_trunks, OFFSET is also volatile so isn’t a good alternative to the ‘Indirect’ function! This is still a valid method to create the dynamic data table if volatility is not an issue, but a better method can be found here – although unfortunately ‘SUMIFS’ hasn’t been used in this instance.
UPDATE: Reddit user /u/ubbm has come to the rescue! The example below with OFFSET can be replaced with an INDEX function. INDEX can be used in a very similar way to OFFSET; if you specify an array and then only choose a row number or column number, the output will be the entire row or column within the array. So for this example the following can be used instead of OFFSET:
See the full comment for a break down of the logic behind this method of using INDEX.
The structure of the OFFSET function is:
- =OFFSET(Reference Range, Number of Rows to Move from Reference, Number of Columns to Move from Reference, [OPTIONAL] Height of the Reference, [OPTIONAL] Width of the Reference)
- So, our ‘OFFSET’ function is: = OFFSET(Measure Name Column (1 column before the date columns), Move 0 Rows, Find ‘Apr-15’ in the date headings and move that many columns across). We start one column before the date headings as ‘MATCH’ returns 1 if it finds a match in the first cell of the range.
So our full formula is:
=SUMIFS(OFFSET(MasterBacksheet!$C$2:$C$31,,MATCH(‘Output Sheet’!C$3,MasterBacksheet!$D$1:$AA$1,0)),MasterBacksheet!$C$2:$C$31,’Output Sheet’!$H$17,MasterBacksheet!$B$2:$B$31,’Output Sheet’!$B4)
This formula can then be copied across the entire data table.
And there we have it! This of course is just one way of going about solving this issue, and part of the beauty of Excel is the ability to bend it to your will when you know all the different functions and tricks of the trade. If you have any other ways in which you would go about creating a dynamic data table, you can share it in the comment section below. Any feedback on my methodology is also welcome!
You can download the finished workbook here.