I need some assistance with modifying the VBA in an existing XLS (see spreadsheet).
Attached XLS contains sample data where cells A2:C2 are drop-down menus.
Combo box (A2) currently shows four (4) values. If the value does not exist, user can simply enter the new value and the, e.g., new (5th) values becomes available. Same applies to B2 and C2. So far so good.
Instead of using the "California" (example) drop-down box in only A2, I want to be able to copy/paste the combo into A3:A10. At this time all previously existing drop down values are available. However, in the event a user adds a new value to combo A10, it will only be available in A10 (vs. A2 through A10).
My question: How can the code be modified so that the entire "California" combo values are not added to the "Data Validation List" but instead to e.g. a new sheet in column A. Then, maybe I could use a dynamic lookup (Offset) where any new values would update the combo (which is linked to that range). I hope that makes sense.
Ultimately, I simply need to be able to have a combo where new values can be dynamically added to the combo and all combos in the same column will show the newly added value. Any idea how to accomplish this modification?