Forum Discussion

rmmwilg's avatar
rmmwilg
Brass Contributor
Jun 20, 2019
Solved

Auto select drop down menu item based on a cell value

Hi all,   Is there a way to populate or auto-select a drop-down menu item in one cell based on another cell's value? eg. The difference between two other cells is a positive value, so I would like...
excel
Macros and VBA
office 365
  • Twifoo's avatar
    Twifoo
    Jun 22, 2019

    rmmwilg 

    In the attached file, the formula in the source box of the drop-down list in I9 is: 

    =IF(((H9="")+(H9=0))*(I9=""),Codes,IF((H9>0)*(I9=""),PosCode,IF((H9<0)*(I9=""),NegCode,"")))

    Note the following defined names therein: 

    NameFormula
    Codes=Sheet1!$A$2:$A$14
    NegCode=Sheet1!$A$4
    PosCode=Sheet1!$A$9
Twifoo's avatar
Twifoo
Silver Contributor
Jun 20, 2019
If you’re referring to alternative lists, you may enter a formula similar to this in the source box:
=IF(A2>0,
List1,
List2)
rmmwilg's avatar
rmmwilg
Brass Contributor
Jun 21, 2019
Not sure if I follow you Twifoo. What I have is a drop-down menu in one cell, that is necessary so that the user can make other selections, but within the selections available in that drop-down menu, under certain conditions, it would make things a lot more user friendly, if the drop-down menu would auto-select two of its items if the value of another cell were positive or negative.

Resources