Adding new data to a drop down list

[rlkerr]

I have a drop down list already made and works fine. What I need to happen is to add a new entry if it is not on the drop down list, without updating my validation rule.

Example:
Validation Rule is E1:E3.
Apple
Orange
Grapes

Drop down list in A1, I need to enter Peach, but add it to e1:e3 without updating the validation list. I have it where I can enter Peach, but it want show up in the drop down list for future entries.

Can this be done?

[tigeravatar]

rlkerr,

Use a dynamic named range
http://support.microsoft.com/kb/830287

[TMS]

Create a dynamic named range called "Fruit", without quotes. The refers to formula would be:

=$E$1:INDEX($E:$E, COUNTA($E:$E))

Then your validation rule becomes "=Fruit", again, no quotes.


Regards, TMS

[rlkerr]

I followed the instructions, but when I try to type in Peach is gives me the error "The value you entered is not valid."

[TMS]

Probably best to post a sample workbook.

Regards, TMS

[rlkerr]

I have attached a very simple plain example of my spreadsheet, so you can see my drop down list I already have.

What I am looking for is in E6, B11, and B12 drop downs is to be able to type in a new location and have it add it to the list so it will be there for next time. I don't know if this can be done or not, I just thought it would be great if it could.

[TMS]

That's not the way Data Validation works. The basic idea is that you can only select something that already exist in a list somewhere.

The information provided will enable you to create a dynamic named range ... but you would still have to add entries to that list before they will appear in the DV selection list.

I posted this recently in the Tips and Tutorials forum. It might be what you are looking for:

http://www.excelforum.com/tips-and-t...tion-list.html


Regards, TMS

[rlkerr]

Thank you for your help. I need to mark this solved, but I can't see the solved button. My apologies, I thought I followed the instruction.