Dependant dropdown list based on cell value

Hi Everyone,

If I could get some help with the following, that will be great. I have attached the excel file for your reference.

Basically, what I am trying to do is create a dropdown list based on the value that I enter in cell A18 (Shaded in Grey). If the value entered is between any of the ranges in cells A2:A18, Excel will choose the lower limit cell. For example if I input 0.25 in cell A18, it should fall under cell A2 since that is the lower limit. Excel should then select the corresponding device from column B and create a dropdown list in cell B18.

I can get a dropdown list; however, I can't get it more than one option in the dropdown list. For example, if I input 2.2 in cell A18, Excel should select
B8:B10 and put those devices in the dropdown list. Then, based on the selection in the dropdown list, Excel should select the respective clearing time and put it in cell
C18.

I have been trying to figure this out and I have a feeling it's easy using Validation Data with Index/Match. But, no luck so far.

Any help is appreciated.

Thanks

Hi,

For the data validation to return the devices try:

=OFFSET($A$1,MATCH($A$18,$A$2:$A$14,0),1,COUNTIF($A$2:$A$14,$A$18),1)

As for the values in A18, I noticed that you are not using the same values that are in A2:A14 (i.e. 0.30 in A18 won't match anything in A2:A14 because that value is 0.301). I'd suggest using a list of unique values from A2:A14 to populate another drop down list in A18. That way your values will match.

HTH
Steve