06-19-2019 05:23 PM
06-19-2019 05:23 PM
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
06-19-2019 09:37 PM
@rmmwilgYou may use IF function instead of the drop down, similar as the following:
As A2 is the cell of the positive or negative value, and the formula is written instead of the drop down
06-19-2019 10:59 PM
06-20-2019 08:46 PM
06-20-2019 08:50 PM
06-21-2019 02:45 PM
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.
06-21-2019 10:00 PMSolution
In the attached file, the formula in the source box of the drop-down list in I9 is:
Note the following defined names therein:
06-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!
06-22-2019 10:39 AM - edited 06-22-2019 10:39 AM
06-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?
06-22-2019 10:47 AM
06-22-2019 11:46 AM
06-22-2019 12:37 PM
So working off of what you suggested I came up with:
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!
06-23-2019 01:48 PM