Forum Discussion

ChristianHin's avatar
ChristianHin
Copper Contributor
Jul 06, 2022

Is it possible to create a function to verify if up to 6 cells are compatible with each other?

With excel limitations (Without use of macros), is it possible to create a compatibility check from 6 different tables?

Case is that I have a report consisting of several drop-down menus/lists that users need to select from.
There are in total 6 drop down menus that need to be selected.
Each menu has its own text based list that is not compatible with all items from other drop down menus.
I am looking for a way to highlight when something is not compatible.

Short example:
Dropdown menu 1 has the list: a, b, c
Dropdown menu 2 has the list: 1, 2, 3
a is compatible with 1 and 2
b is compatible with 3
c is compatible with 1, 2 and 3

Desired outcome: If person tries to select "a" and "3", the cell containing "3" will format to red filling to highlight that this combination is not compatible.


Detailed example using 'select your dinner' as goal:
Rules:
Only juices can be selected for breakfast, whereas dinner can select all drinks.
Breakfast menu is limited to potatoes and bacon for sides.


 

I've attempted this using a vlookup function together with a compatibility table, which does give the desired result formatting wise, but is relying on the drop-down values being numerals rather than text. And a whole lot of compatibility tables.

I've also tried using data validation to dynamically change drop-down list 2 based on your choice in drop-down list 1, but this does limit being able to select the wrong item, which I would like to be able to. The desired outcome is to tell the user that they have selected a not compatible item rather than removing them as an option all together.


Would highly appreciate if any gurus out there would be willing to give this a go as im truly stuck.
Thanks for any help!

developer
excel
Formulas and Functions

2 Replies

Sort By
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jul 06, 2022

    ChristianHin 

    Adding to Riny_van_Eekelen 's suggestion, you could add a named formula

    "IncompatibleSelection"
= OR(ISERROR(
     FILTER(Table1, (Table1[Menu1]=Option1)*(Table1[Menu2]=Option2))
  ))

    to control conditional formatting.  It is now also possible to control the options available within a single dataset so that each choice restricts the other options (eg selecting soda will prevent breakfast from being selected as well as the other way around).  I understand that this is not what you require though.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jul 06, 2022

    ChristianHin 

    If you are using Excel for MS365 or 2021, I'd suggest you use UNIQUE and FILTER and a structured table to define all the possible combinations. Otherwise, create the drop-down list with named ranges.

     

    Both methods are demonstrated in the attached file.

     

    Otherwise google for "dependent dropdown excel" and you'll find plenty tutorials on how to work with these.

Resources