excelexcel-formuladrop-down-menuexcel-indirectexcel-tables

Dropdown list with a conditional statement

I have some materials in column B, a few among these are in a Table definition called Material_List. In D49 I am trying to write a conditional statement such that, if the data in B49 already exists in the table definition, then print the header name or else INDIRECT($49). C49 has the independent dropdown list and D49 will be the dependent.

In D49 I have used the following formula within the Data-->Data Validation-->Source=

=IF(MAX((ISNUMBER(MATCH(Material_List;$B49;0))*COLUMN(Material_List)))=0;
INDIRECT($C49);
INDEX(Material_List[#Headers];1;MAX((ISNUMBER(MATCH(Material_List;$B49;0))*
COLUMN(Material_List))))))

with Allow=List. But it says Error "There is a problem with this formula"

When typed the following formula in cell D50 directly, it works well but obviously without dropdown.

=IF(MAX((ISNUMBER(MATCH(Material_List;$B50;0))*COLUMN(Material_List)))=0;
INDIRECT($C50);
INDEX(Material_List[#Headers];1;MAX((ISNUMBER(MATCH(Material_List;$B50;0))*
COLUMN(Material_List))))))

I am trying to build a dropdown list based on the mentioned criteria. could anyone please tell what is wrong with my formula? Data table

Solution

  • I think the main issue with your formula is that you cannot use table references in the data validation.

    Don't ask me why. I think it is just an outstanding Excel bug which hasn't been fixed yet. Please see this link for further info: https://exceloffthegrid.com/using-an-excel-table-within-a-data-validation-list/

    The best way I have found to work around this is to create a named range which refers to the table references you need ("Material_List" and "Material_List[#Headers]" in your case). Then you can use those named ranges in your data validation instead of the table references directly.

    However, I think there are also other issues with your formula. For example, this part:

    MATCH(Material_List;$B50;0)

    Normally a MATCH would be in the format of:

    MATCH(<single value to look for>, <range to look in>, 0)

    You appear to have that reversed, meaning that it should always return a #VALUE! error.

    Also, I don't think you can use match on a 2D array, so if your "Material_List" table is more than a single column, that would also cause it to return a #VALUE! error.

    UPDATE:

    The way I would tackle dependent dropdowns would be as follows. I would create a "Material_List" table similar to below (could be on a hidden sheet):

    enter image description here

    Then I would create 3 named ranges. One for the table body range, called "MaterialList_TblRange":

    =Material_List

    One for the table header range, called "MaterialList_TblHeaderRange":

    =Material_List[#Headers]

    And one to refer to the dependant dropdown options, called "DropDownOptions" (this is by far the most complicated part):

    =INDEX(MaterialList_TblRange,1,MATCH(Sheet1!$B23,MaterialList_TblHeaderRange,0)):INDEX(MaterialList_TblRange,COUNTA(INDEX(MaterialList_TblRange,1,MATCH(Sheet1!$B23,MaterialList_TblHeaderRange,0)):INDEX(MaterialList_TblRange,ROWS(MaterialList_TblRange),MATCH(Sheet1!$B23,MaterialList_TblHeaderRange,0))),MATCH(Sheet1!$B23,MaterialList_TblHeaderRange,0))

    I will explain what this is doing in a moment.

    The last step is to set up the data validation where we want our lists.

    Where we want the master lists to appear, we can simply enter:

    =MaterialList_TblHeaderRange

    And the defendant dropdown validation can be entered as:

    =DropDownOptions

    This is the result:

    enter image description here enter image description here

    Now back to the long "DropDownOptions" named range formula...

    Basically, we use INDEX:INDEX to select the first/last cell in the range we want to use in out dropdown.

    The first INDEX:

    =INDEX(MaterialList_TblRange,1,MATCH(Sheet1!$B23,MaterialList_TblHeaderRange,0))

    Simply selects the first cell from the column whose header matches the selection in our first dropdown.

    The second index does the same, except that instead of selecting the first cell in the column, it counts the number of cells that contain text and uses that as the last cell in the range.

    This does mean that we mustn't have any gaps in this table, otherwise an option might be missed off the end.

    I hope this makes sense.