Excel Forum

Searchable Drop-Down List

llogballig

Active Member

Members

Forum Posts: 4

Member Since:
May 23, 2020

Offline

May 23, 2020 - 1:51 am

Hi All,

I've created a searchable drop-down list that populates a graph based on the assay selected. I'm trying to anticipate the event where no assays are done for an entire month or months. I'd like to compute "NA" for the time column if for any month(s) there is no data and still have the graph populate properly. I tried adding iterations of =if"&Metrics!G9,Table1[Date],"=””,NA() with no success. I have attached the workbook for your review. Any help you guys could provide will be greatly appreciated.

Thanks in advanced.

Purfleet

England

Member

Members

Trusted Members

Forum Posts: 414

Member Since:
December 20, 2019

Offline

May 23, 2020 - 4:11 pm

So as an example if i changed all the Column Assays for march to something else (bob) and then selected Column in the drop down you get #div/0! errors - is that want you want to stop?

If so could you just iferror & NA()?

=IFERROR(IF($C$5="(All)",AVERAGEIFS(Table1[Time],Table1[Date],">="&Metrics!G11,Table1[Date],"<="&EOMONTH(Metrics!G11,0)),AVERAGEIFS(Table1[Time],Table1[Assay],Metrics!$C$5,Table1[Date],">="&Metrics!G11,Table1[Date],"<="&EOMONTH(Metrics!G11,0))),NA())

The graph will just have a blank area

In the data grid i think you will need to use aggregate instread of Min/max/Average as aggregate can ignore errors

=IFERROR(IF(AGGREGATE(15,6,$H$9:$H$20,1)=$H9,AGGREGATE(15,6,$H$9:$H$20,1),NA()),NA())

=IFERROR(IF(AGGREGATE(14,6,$H$9:$H$20,1)=$H9,AGGREGATE(14,6,$H$9:$H$20,1),NA()),NA())

=AGGREGATE(1,6,$H$9:$H$20)

Let me know if i am on the right lines

Purfleet

llogballig

Active Member

Members

Forum Posts: 4

Member Since:
May 23, 2020

Offline

May 27, 2020 - 1:51 am

Hi Purfleet,

Thank you so much for your response. I really appreciate it. I'll look into including the adding the aggregate instead of the max/min. I'm not sure if your suggestion about altering the assay column will solve my issue. Given the current pandemic, in most places no work is being done. So say for the months of March and May there will be no data for those two months. I'd like to amend the formula so that an "NA" populates for the time column for those months so that the graph display correctly. Thanks again for all your help.