SOLVED

Auto select drop down menu item based on a cell value

Copper Contributor

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 the drop down menu in another cell to select one value, or a different one if the difference is a negative value

15 Replies

@rmmwilgYou may use IF function instead of the drop down, similar as the following:

=IF(A2>0,Value 1,Value2)

As A2 is the cell of the positive or negative value, and the formula is written instead of the drop down

If you’re referring to alternative lists, you may enter a formula similar to this in the source box:
=IF(A2>0,
List1,
List2)
Hi Ayman, the challenge is that I have to retain the drop down menu, since it has other uses in addition to this 'autofill' request. What I'm hoping for is that there's an ability to make a drop-down menu auto select a certain item based on another cell's value (positive or negative)
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.
Can you please attach a sample file to illustrate what you want to achieve?

@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.

best response confirmed by rmmwilg (Copper Contributor)
Solution

@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 

 

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!

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.

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?

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
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.

@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!

You’re very much welcome!

@rmmwilg 

Hello Everyone

I am privileged to join this amazing community of Excel users today only.

here is a tutorial I created sometime ago on dependent drop lists:

https://www.youtube.com/watch?v=0MuQfUJbycQ

Hope this helps

Thanks

Nabil Mourad

Creating dependent drop lists. Using drop lists in Excel creates a dynamic professional worksheet. In a previous tutorial I explained how to create a drop list "Data Validation". In this tutorial we'll go one step farther by creating 2 drop lists where the contents of the second drop list change ...
1 best response

Accepted Solutions
best response confirmed by rmmwilg (Copper Contributor)
Solution

@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

View solution in original post