I'm trying to create a dashboard in MS Excel 2016 such that there are 4 areas that display a count of specific items and below that, list the items determined from the count (up to 8 items). The issue I'm running into is the CSE array formulas don't seem to be doing what I think they should, and I'm hoping someone can point me in the right direction.
The spreadsheet (Attached) consists of 3 tabs, Data (the Name and Expiration table), Dashboard (the dashboard view), and Formulas.
Data tab contains a table (Table1), which consists of 2 columns called Vendor(Text) and Expiration(Short Date). I've used vendor titles that display the days out from today, but they are arbitrary and could be anything.
Dashboard tab contains the 4 items for display (Expired, Expires 0-30 days, Expires 30-60 days, Expires 60-90 days) where I display a COUNT of the respective items and below it, list the items that fall within the count.
Formulas tab contains named items which I use to reference in the Dashboard formulas:
Today's date - Name: _today Formula: TODAY()
30 days from today - Name: _30 Formula: TODAY()+30
60 days from today - Name: _60 Formula: TODAY()+60
90 days from today - Name: _90 Formula: TODAY()+90
Getting the count is easy enough and it is displaying correctly in the dashboard using countifs and changing the conditions as needed:
Example for 0-30 days: =COUNTIFS(Table1[Expiration],">="&_today,Table1[Expiration],"<="&_30)
The issue I'm having is generating and listing the array of associated item Names below it. It works well with the expired items using:
{=IFERROR(INDEX(Table1[Vendor], SMALL(IF(Table1[Expiration]<=_today, MATCH(ROW(Table1[Vendor]), ROW(Table1))), ROW(A1))),"")}
However, when I start to add the ranges into the array formula, it's only showing the first item from the list which is correct from 0-30 days, but incorrect for other ranges.
Example 30-60 days:
{=IFERROR(INDEX(Table1[Vendor], SMALL(IF(AND(Table1[Expiration]>_30,Table1[Expiration]<=_60), MATCH(ROW(Table1[Vendor]), ROW(Table1))), ROW(A1))),"")}
Any assistance in the array formulas to list the items in 0-30, 30-60, and 60-90 dashboard columns would be greatly appreciated.
Thanks to the community and Cheers!
The spreadsheet (Attached) consists of 3 tabs, Data (the Name and Expiration table), Dashboard (the dashboard view), and Formulas.
Data tab contains a table (Table1), which consists of 2 columns called Vendor(Text) and Expiration(Short Date). I've used vendor titles that display the days out from today, but they are arbitrary and could be anything.
Dashboard tab contains the 4 items for display (Expired, Expires 0-30 days, Expires 30-60 days, Expires 60-90 days) where I display a COUNT of the respective items and below it, list the items that fall within the count.
Formulas tab contains named items which I use to reference in the Dashboard formulas:
Today's date - Name: _today Formula: TODAY()
30 days from today - Name: _30 Formula: TODAY()+30
60 days from today - Name: _60 Formula: TODAY()+60
90 days from today - Name: _90 Formula: TODAY()+90
Getting the count is easy enough and it is displaying correctly in the dashboard using countifs and changing the conditions as needed:
Example for 0-30 days: =COUNTIFS(Table1[Expiration],">="&_today,Table1[Expiration],"<="&_30)
The issue I'm having is generating and listing the array of associated item Names below it. It works well with the expired items using:
{=IFERROR(INDEX(Table1[Vendor], SMALL(IF(Table1[Expiration]<=_today, MATCH(ROW(Table1[Vendor]), ROW(Table1))), ROW(A1))),"")}
However, when I start to add the ranges into the array formula, it's only showing the first item from the list which is correct from 0-30 days, but incorrect for other ranges.
Example 30-60 days:
{=IFERROR(INDEX(Table1[Vendor], SMALL(IF(AND(Table1[Expiration]>_30,Table1[Expiration]<=_60), MATCH(ROW(Table1[Vendor]), ROW(Table1))), ROW(A1))),"")}
Any assistance in the array formulas to list the items in 0-30, 30-60, and 60-90 dashboard columns would be greatly appreciated.
Thanks to the community and Cheers!