Data Validation is pretty useful for making sure that appropriate data is entered into a cell. If you’ve been reading this blog, you know I’m fond of DV despite its problems.
One of the things that I like best about DV is the In Cell Dropdown. That little dropdown arrow that goes away when you’re not in the cell. Sometimes I use Data Validation to have a handy list in the cell, but I don’t really want to validate the data. I want to be able to type in anything I want, but still have some commonly used items in a list.
For me, Data Validation is useful in that situation also. The key is to remove the error portion. Take this example where I have a list of three cities in the DV for cell A1. I use these three cities frequently, but I need to be able to type in less frequently used cities also.
On the Data Validation dialog, simply select the Error Alert tab and remove the checkbox so that no errors are generated.
While you’re there, check out the other “error” options.
8/18/04
Dick:
I greatly enjoy your website. Keep up the excellent work.
In your recent article using Data Validation, you talked about using Data Validation to obtain a list to choose from. If you have a list in place, why not right click the empty cell at the bottom of the column and choose pick from list?
Thanks
This is a great tool. I have recently used this tool with the SUBTOTAL function to sum a list or give me an average of the list. Select SUM from the dropdown. SUBTOTAL is linked to this cell and does a VLOOKUP of Sum to find the number 9 then sums the cells.
Interesting article, I had wondered how to do that, and in particular, add new items to the list (a quick dynamic range in the listsource should solve that).
I have a wee puzzle for you, if your interested;
I have a column (Sheet2!F7:F50) with the following data validation list source, which basically allows users to select one of the values from another worksheet (PCOCol = Sheet1!A6:A106).
=OFFSET(PCOCol,0,0,COUNTA(PCOCol),1)
This works ok, but I want to weed out values that have already been used, eg if the list is {“Apple”,”Banana”, “Pear”}, and I select “Apple” in cell F7, I want the list to change to {“Banana”, “Pear”} so I can’t have 2 “Apple”s.
Thanks in advance. :)
Scott,
It’s easy to prevent duplicate entries. That’s just a data validation tickbox.
But to provide the user with a dropdown of just unique entries… no easy task without helper columns.
An example is here:
Contextures Sample Excel Spreadsheets – “Extract List of Unique Items”
Cheers,
Rob
You’re telling me! I’ve been pulling my hair out for dyas! :)
Had a look at the site. That’s not quite what I’m trying to do (I can manage that much – just).
Basically, what I think I need to do, is the equivalent of a set/sql SUBTRACT operation on 2 dynamic ranges. (I have no idea if this is even possible in excel)
{items in list1} – {items in list2}, where list 1 items are in the drop-down list.
I already know that list1 items are unique, and list2 will obviously contain all the items I have selected, and I just need to remove these from my dynamic range list1.
I’m starting to lean towards doing this using vba to recreate the named range using onValChange etc, something like:
for each x in list1, add x to myArray…
for each y in list2, if y in myArray, remove y…
define list 3 = myArray(1), myarray(2),…
You get the idea (I hope), desite my shady vb skills. :D
Scott
i have a list of 900 names in one workbook (not a worksheet). I want the selection list of only persons whose names started with ‘A’ or any selection criteria in data validation.
or
any other method you prefer
I also have a similar problem.
Excel 2003 will not allow you to use validation across different workbooks.
Is there an easy way to create a drop down list (error checking is optional)? Or is a VBA the only way.
If VBA, what is the code?
Any help someone could provide would be great.
Thanks.
Hi
I am using Excel 2003 SP2. When I use macros to set the value on a cell having data validation, I get an error.
My cell has two possible values Pass, Fail and I try to set the value of the cell using:
Range. Cell(1,1).Value = a string thats either “Pass” or “Fail”
Can someone please help?
Thanks