Forum Discussion
Auto select drop down menu item based on a cell value
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:
Name Formula Codes =Sheet1!$A$2:$A$14 NegCode =Sheet1!$A$4 PosCode =Sheet1!$A$9
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.
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:
| Name | Formula |
| Codes | =Sheet1!$A$2:$A$14 |
| NegCode | =Sheet1!$A$4 |
| PosCode | =Sheet1!$A$9 |
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.
- 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