A simple dashboard

We are leading up to create a simple dashboard, but we still need to cover a couple of things:

The "#" symbol

When referencing an array, you can use the # symbol in the topmost row, which will cascade down. Let me explain further.

Remember our Sequence() discussed in Part 3? If you want to reference the array in S4, you can type:

=S4#

in U4, and the values will cascade down. So you don't have to copy/paste the formula down. If you want to add one day to the end-of-month calculation, just enter:

=S4#+1

Which leads us to our dashboard example.

Dashboard Example

Since we have the honey data in a table named "tbl_Honey", we don't have to worry about how many rows are in the honey dataset. We can just reference tbl_Honey[Ag District] versus Data!H2:H2275.

We can use Data -> Validation to pick out the Data Item (C3) and the Unique() function to pull the AG District (C7). Then we can populate the years used in D6 by entering:

=TRANSPOSE(SORT(Reference!B6#,1,-1))

To determine the total amounts in D7:G12 you need to use the Sumifs() function:

=SUMIFS(tbl_Honey[Value],tbl_Honey[Ag District],$C7#,tbl_Honey[Data Item],$C$3, tbl_Honey[Year],D$6)

Note how we used the $C7# to get the AG District.

You can enter an "x" in B7:B12 to select which AG District to show in C17. Then in C17 enter:

=IFERROR(SORT(UNIQUE(FILTER(tbl_Honey[County],tbl_Honey[Ag District]=XLOOKUP("x",$B$7:$B$12,$C$7:$C$12,"",0)))),"")

This formula will pull the first AG District for the selected AG District. You can read the formula as sort the unique County in the table tbl_Honey where AG District = "x". If nothing is selected return a blank.

I don't know if I'm finished with this blog entry, but I think there is enough here for you to create your own simple dashboard.