Auto add drop down list

[blundastruck]

I've got a drop down list that takes the values of one sheet and uses that to display my options.

EX: I have a list of colors:
Red
Orange
Yellow

But now, I want to add Green.

I type it into my sheet, but the drop down list won't take it. The issue is the list is set to only look at the cells I told it to, but I've got WAAAAAY too many cells that use this data validation tool to change each cell to add that extra field.

Is there a smarter way I can use the data validation tool to automatically add list options? Mind you, I don't have any VBA experience but that's fine because I want to learn.

 

[Joe4]

How exactly have you set up this Data Validation?
Are you referring to a range on a sheet?
If so, then in you insert a new cell in the middle of that range (i.e. between the"Orange" and "Yellow" entry) and enter the new "Green" value there, I believe Excel will automatically "grow" that range in all your Data Validation cells will automatically include "Green" as an option.

Alternatively, insert of referring to the range addresses in the Data Validation formula, you could use a "Named Range" instead, and use that "Named Range" in the Data Validation formula.
Then, if you have to add more entries in your list, you simply need to adjust the Named Range to include those new entries, and it will be reflected in all your Data Validation cells.

 

[blundastruck]

How exactly have you set up this Data Validation?
Are you referring to a range on a sheet?
If so, then in you insert a new cell in the middle of that range (i.e. between the"Orange" and "Yellow" entry) and enter the new "Green" value there, I believe Excel will automatically "grow" that range in all your Data Validation cells will automatically include "Green" as an option.

Alternatively, insert of referring to the range addresses in the Data Validation formula, you could use a "Named Range" instead, and use that "Named Range" in the Data Validation formula.
Then, if you have to add more entries in your list, you simply need to adjust the Named Range to include those new entries, and it will be reflected in all your Data Validation cells.

Your "insert into list" option worked perfectly, so I'm marking this as answered. I don't know about the Named Range trick, but will search and learn. Thank you!

 

[Joe4]

You are welcome!
See here for named ranges: How to Create Named Ranges in Excel (A Step-by-step Guide)