Formula Criteria - IS ANY OF based on Multi-Select Dropdown

P_Youderian

Hey,

So I'm going to guess that the answer is no, but I'm going to hope the answer is yes.

We have a board that is used to help track hot or critical inbound items. The goal is to retrieve dates from our, lets call them 'Build Trackers', based on a series of criteria in order to help inform the priority and urgency of those items, and on the base level, that is easy enough. However, my current solution, while usable, is less than ideal.

Current Version:

* This is where my current formula lives. It uses the Priority Build field as the identifying criteria for looking up dates on the Build Trackers. However, it requires that people keep the field up to date as far as which Build currently has the highest priority.

Ideal Version:

I would love to find a way to be able to check the earliest date for each build and then take the earliest of those. Which brings me to wondering if there is an inverse to the HAS or CONTAINS functions, the multi-select dropdown is clearly aware of containing several distinct values, but I have no idea if it is possible to define a criteria in a formula as 'is any of the values contained'.

Mike TV

@P_Youderian

I do not quite understand what the formula is supposed to be identifying and the results it's supposed to output based on your examples. Could you provide more info please of what the criteria it would need to review and what a successful output would be?

Genevieve P.

Hi @P_Youderian

You are correct, there currently isn't a way for a formula to look into a multi-select cell and parse out each individual option to search through a single column for a match in another sheet. The formula would look for the same combination in a single cell in the other sheet.

However, there may be another way we can pull this information. What if we had a multi-select column in your sheet that lists out the Builds to identify the Item Numbers associated with each Build?

Ex:

=JOIN(COLLECT({Item Column Sheet 2}, {Impacted Builds Sheet 2}, HAS(@cell, [Build List]@row)), CHAR(10))

This would associate each of your Items with a single build. Then in Sheet 2, you can use a MIN(COLLECT formula to bring back the minimum date by the item instead of the build:

=MIN(COLLECT({Date Column Sheet 1}, {Multi Select Item Number Sheet 1}, HAS(@cell, [Item Number]@row)))

I've set this up in the same sheet, but pretend my blue columns are Sheet 1 and the white columns are Sheet 2:

Let me know if this makes sense and will work for you!

Cheers,

Genevieve