• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help listing elements of an array

marckw

New Member
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!
 

Attachments

  • Expiration-Demo.xlsx
    21 KB · Views: 3
Back
Top