Data Validation (2003)

Topic

New Reply

I am trying to add a cell validation where the user can type in a number in one cell but if the number doesn’t exist in another column, the entry would be invalid. Is this possible? I tried using the custom setting and an “exact” function but it didn’t work.

I would appreciate the help.

Reply | Quote

Replies

GoodToGo,
Set data validation of the cell to List and highlight the numbers that are valid.

Chuck

Reply | Quote

If I do it that way, the number cannot be typed into the cell and they would have to choose from the drop down list. Is there not another way?

Reply | Quote

Gee – it works for me; the values in the list are available either through the drop-down or when I type them in.

You can define the validation list either by XL renge references ($A$1:$A$15 style) or as a defined range name. The attached file has both. If you may be adding items to (or deleting – but then you get into ‘orphan’ problems) a defined name using a dynamic range name will work well.

Reply | Quote

Thanks to all, it does work this way. I believe the problem was the entry field was set as text and it seems to be working now.

Thanks again to all!

Reply | Quote

I have just discovered a problem with my cell validation. In the entry cell, if you type a number such as 01 and this number is in the validation list, it still returns an error message of an invalid entry. This happens wheather or not a zero is put in front of the number. I have checked the formatting of the cells to validate and they were both set the same, text, number, general. Entering any other number from 10 onwards is not a problem, why then will it not accept an entry less than 10?

Reply | Quote

If your list has a a text value of “01” and not the number 1 then you must enter a text value for validation:
Enter the single quote(‘) followed by the “01”

Does the lookup have text or numbers in it or a combination of both?

It may be easier to keep your “lookup list” all numbers (you can format cells, custom, 00 to display as “01” and still keep it a number), so that the user does not have to enter text.

Steve

Reply | Quote

I can’t seem to get them to enter regarless of the cell formatting (number, text, custom etc). I am attaching a sample of the spread sheet and it is column C that I am trying to validate. As you can see, these are codes not numbers but definately, entering as numbers is so much easier.

Thanks

Reply | Quote

The first 9 values in column G were text and the rest were numeric. This will cause all kinds of problems if you use these with data validation. Does the attached do what you want?

Reply | Quote

Yes it does do what I want, however when I tried to do the same thing on my actual spreadsheet, no matter how I formatted either coloumn, it still would not work! Finally, I simply retyped the code list with a custom setting and it is now working great.

Thanks for all your help!!!!!!!!

Reply | Quote

Its too late now, but you could have done what I did. Select the cells containing the text values, make sure that the cell format is “General”, and then select “Text to columns…” from the data menu and click on the finish button in the dialog box.

Reply | Quote

Thanks, that will certainly be something to bear in mind for next time.

Reply | Quote

Assume the cell to validate is A1 and the values to lookup are in C1:C10. Then you can use this custom validation:

=ISNUMBER(MATCH(A1,$C1:$C10,0))

Steve

Reply | Quote