Jun 19 2019 05:23 PM
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
Jun 19 2019 09:37 PM
@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
Jun 19 2019 10:59 PM
Jun 20 2019 08:46 PM
Jun 20 2019 08:50 PM
Jun 20 2019 09:44 PM
Jun 21 2019 02:45 PM
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.
Jun 21 2019 10:00 PM
SolutionIn 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:
Name | Formula |
Codes | =Sheet1!$A$2:$A$14 |
NegCode | =Sheet1!$A$4 |
PosCode | =Sheet1!$A$9 |
Jun 22 2019 09:56 AM
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!
Jun 22 2019 10:39 AM - edited Jun 22 2019 10:39 AM
Jun 22 2019 10:45 AM
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?
Jun 22 2019 10:47 AM
Jun 22 2019 11:46 AM
Jun 22 2019 12:37 PM
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!
Jun 22 2019 07:15 PM
Jun 23 2019 01:48 PM
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
Jun 21 2019 10:00 PM
SolutionIn 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:
Name | Formula |
Codes | =Sheet1!$A$2:$A$14 |
NegCode | =Sheet1!$A$4 |
PosCode | =Sheet1!$A$9 |