"I use data validation drop down lists, but I would like to be able to type new options into these cells and have what I typed become a permanent option in the drop down list itself so all cells using that DV list are updated with this new option in real time."
Technique uses named ranges for the Data Validation List source
If a cell with a DV list is used to allow a new entry not in the DV list, the item is added to the drop list, too
The Drop Down lists self-expand
PART 1 - Dynamic Named Ranges
Instead of hardcoding the range of cells that make a list of options for a data validation source, we use a formula instead. The OFFSET() function allows functions to be used that will result in a "range" being defined by however many items are in a particular column.
In the example sheet attached above, we see the named range Users is defined with the formula:
This formula anchors the range at cell A2, the uses the COUNTA() function to count the number of used cells in column A and uses that for the ROWS parameter, automatically causing the range to expand simply by tpying more adjacent entries into column A. We subtract 1 from the COUNTA because column A has a title cell in A1 that we are not using.
There are additional dynamic named ranges for Animals and Cars in the sample file that do the same thing in columns B and
PART 2 - The Form Cells
Typical settings are used for a Data Validation "list" using the named range as the source.
The key is to remember to select the Error Alert tab and uncheck the "alert" option, this allows free typing in the cell(s), too.
PART 3 - VBA code to add to the named ranges when new items are typed in
The following code is installed into the Form sheet module. Right-click that sheet tab and select View Code to see the code module in the example sheet.
Nothing says "thanks" like a steak dinner!