Excel Custom Data Validation

Question

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

Below is Validation Criteria:

Should be a numeric Number
Size/Length should be equals to 9

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.

chris neilsen · Accepted Answer · 2013-07-17 05:49:06Z

2

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)

answered Jul 17, 2013 at 5:49

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

matzone · Accepted Answer · 2013-07-17 04:39:24Z

0

Check this simple validation .. checking Cell D13 ..

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

answered Jul 17, 2013 at 4:39

matzone

5,7293 gold badges19 silver badges20 bronze badges

Comments

teylyn · Accepted Answer · 2013-07-17 11:13:56Z

0

Another way:

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

answered Jul 17, 2013 at 11:13

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Comments

rlc · Accepted Answer · 2015-06-17 22:45:10Z

0

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

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

edited Jun 17, 2015 at 22:45

rlc

2,85620 silver badges23 bronze badges

answered Jun 17, 2015 at 22:03

Tajinder Singh

11 bronze badge

Collectives™ on Stack Overflow

Excel Custom Data Validation

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related