0

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

Improve this question
6
  • I am not entirely sure what you are trying to do. Can you give an example of what you want to be in your desired drop down list? Rather than trying to do the logic in the data validation box, I would suggest composing the list in a hidden sheet and then simply referring to that. Commented Sep 7, 2021 at 14:42
  • As you can see in the image, cells D50 and D51 are identified as Aluminum with the formula. this is because Alu, Aluminium in B50 & B51 belong to a table list with one of the header Aluminum. If ALMG3 in B49 is not available in any of the table list then I would like to activate a dropdown list based on C49. Commented Sep 7, 2021 at 14:54
  • So, If the material is in the list, you don't want there to be a dropdown? And when you do want a dropdown because the material isn't in the list, what options would you like it populated with? Commented Sep 7, 2021 at 14:57
  • If the material is in the list then I don't have to do anything with the cell. So it does not make any difference if I do have a dropdown or not. My intention is, if material is not found, I should feed this material information manually from dropdown list. If the material is not in the list, I would like to populate all the material classes. So for C49 Metalle category, there is a dependent dropdownlist in D49 with options Aluminum, Steel, Copper etc.. Commented Sep 7, 2021 at 15:05
  • Ah. Ok. I see now. So, you are wanting the second dropdown options to vary depending on the selection in the first. Tricky... I will have a play. Commented Sep 7, 2021 at 15:09

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

thank you so much for such a lengthy explanation with an example. All what you meant makes sense to me, but what is $B23 being referred to in the formula? I have some understanding issue with this =INDEX(MaterialList_TblRange,1,MATCH(Sheet1!$B23,MaterialList_TblHeaderRange,0))
The $B23 would be a reference to your first master dropdown. I actually entered it as $B3, but it changes automatically for each row. So that each one refers to a different master dropdown.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.