CODEWITHSUNDEEP
excelexcel-2007
Chandra Manthina
Chandra Manthina

Reputation: 147

Excel Custom Data Validation

I have to restrict some invalid data in a Excel Column.

Below is Validation Criteria:

Ex : valid:602005514, invalid:were,43456 etc.

I have created a Custom Data Validation using below function.

=AND(LEN(INDIRECT(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$","")))=9,
     ISNUMBER(INDIRECT(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$","")))) 
 //here i have to get cell name dynamically. so, i have used ADDRESS function

But its not working as expected. It is not allowing any value in cell. I guess it might be because of LEN function circular reference.

I have tried many ways, but not able to solve it. Please help me to solve this.

Upvotes: 0

Views: 6065

Answers (4)

Tajinder Singh
Tajinder Singh

Reputation: 1

Make sure there are no leading or trailing spaces in the cell: 

=IF(TRIM(A3)=A3,TRUE,FALSE)

Upvotes: 0

teylyn
teylyn

Reputation: 35915

Another way:

=AND(ISNUMBER(A1),A1>99999999)

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53126

You don't need to resort to INDIRECT etc, in you formula just refer to top left cell in the range you are applying the validation.to. Excel will adjust for the othger cells in the range. When entering cell references don't use absolute references, ie no $'s

Eg select a range starting at cell A1, and set data validation formula to

=AND(ISNUMBER(A1),LEN(A1)=9)

Upvotes: 2

matzone
matzone

Reputation: 5719

Check this simple validation .. checking Cell D13 ..

=IF(LEN(D13)=9,IF(ISNUMBER(D13),"yes","no"),"no")

Upvotes: 0

Related Questions