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.
  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Jun 21, 2019
    Can you please attach a sample file to illustrate what you want to achieve?
    • rmmwilg's avatar
      rmmwilg
      Brass Contributor
      Jun 21, 2019

      Twifoo 

       

      Hi there,

       

      Yes, see attached. 

      When the amount in column H (CNTRCTL) is positive, I would like the drop-down menu in that same row, adjacent cell to auto select as 'PD PROJ', when negative, as 'CR BIDLN'. This is a payroll help sheet for pilots. The totals in column H are derived from the difference (if there is one) between the associated, row amounts in columns F & G.

      • Twifoo's avatar
        Twifoo
        Silver Contributor
        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

Resources