I had a question form one of my colleagues about this. They have a validation list of products which has been used using for some time, with no new products being added.
Then today- they are informed that the product list would be changing quite frequently with new products coming on stream in short succession, and of course we don’t want to editing validation lists every week. So, wouldn’t it be great of we could just add in new product data to the list and our validation list would just update and expand to include the new products as they are added.
We have already looked at setting up a Data Validation List. If you need to a refresher on this really useful tool then click here.
There is a really great EASY way to do this in Excel (of course)!!
In Excel 2007
- Define your list of data options for your user to input ie your validation list
- Highlight your list
- Hit CTRL+T- this creates a Dynamic Table ( for Earlier version of Excelc CTRL+L)
- State if your table has a header
- Highlight the cells you want to apply the validation to OR Shortcut ALT, D,L
- Select List in validation criteria- allow
- In source highlight your table
- Hit Ok
There you have it, let’s go ahead and test it.
In my example I added a product list containing Widgets 1 to 4, which was the start of my Widget Name Data Validation list.
I created my table with CTRL + T ( for earlier versions of Excel its Ctrl + L) as above and then I applied validation to Cells D2 to D10.
Which when we test- works ( of course!) in cell D2.
After adding in Widgets 5 to 10, if I go back to my cells D2, I now have the option of choosing Widgets 1 right through to 10. The validation list has updated itself. Now then how handy is that for users? Problem Solved. Job Done.
Other Data Validation Posts
1. Setting Up Data Validation Lists
2. Data Validation- check for repeating values