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
rmmwilg's avatar
rmmwilg
Brass Contributor
Jun 22, 2019

Twifoo 

 

Thanks for the work on this; however, I can't get it to work. I also noticed that the drop-down menu seems to now be locked; ie. once a selection is made, it can't be changed. Great tip though on keeping all the entries on a separate sheet!

Twifoo's avatar
Twifoo
Silver Contributor
Jun 22, 2019
What do you mean by “can’t get it to work”? Is the drop-down list not returning the results you expected? I deliberately precluded selecting another item when one has already been selected.
  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Jun 23, 2019
    You’re very much welcome!
  • rmmwilg's avatar
    rmmwilg
    Brass Contributor
    Jun 22, 2019

    Twifoo 

     

    Hi again,

     

    So working off of what you suggested I came up with:

    =IF((SUM(F9:G9)=0),Codes,IF((SUM(F9-G9)>0),PosCode,IF((SUM(F9-G9)<0),NegCode,)))

     

    I didn't realise that one still has to actually select the item from the Drop-down, so that's why I thought it "wasn't working", because the drop-down menu cell was still blank. The above allows the user free use of the drop down unless numbers are entered in the associated F or G column-cells, in which case it restricts the drop-down selection accordingly.

     

    Great stuff! Thank you very much!

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Jun 22, 2019
    I suggest you fill in all possible data and their corresponding results from the drop-down list. Thereafter, attach your sample file again.
    Please note that any change you make in the conditions for the drop-down list shall trigger a corresponding change in the formula in the source box.
  • rmmwilg's avatar
    rmmwilg
    Brass Contributor
    Jun 22, 2019
    Also, presumably, the coding would prevent a different, manual drop-down menu selection as long as there are values in F9 and G( cells forcing it to make thees pre-determined selections? That would be fine
  • rmmwilg's avatar
    rmmwilg
    Brass Contributor
    Jun 22, 2019

    Good Morning Twifoo,

    Yes, when I put values in F9 and G9 to create a summation in H9, the drop-down menu in H9 remains blank. That made me notice though, that we should expand the criterion for this event to only happen when there are values entered in the 'SH' and 'RA' columns (F9 & G9 respectively), so that users can still manually enter other values, manually in the CNTRCTL column (H9) for other drop-down values that they would then select manually.

    Just so I can learn more, could you also go through what the various coding stages you loaded mean?

Resources