Data Validation for a Column that contains reference in 12345.12.12.12 format

[cbarryb]

Hi, is there a way to have data validation on a column, possibly a custom way / formula, that a user has to input the refernce in this format 11111.11.11.11 with the decimal seperators, its the end of the account number first and then todays date at the end 12345.01.06.20. I have tried, but my lack of Excel skills have me failing.

Any help would be grately appreciated.

 

[jimrward]

ok here is one solution which checks the following
length = 14
dots = positions 6, 9, 12
each section is a number
this is a custom option in data validation assuming the data is in A1

=AND(LEN(A1)=14,MID(A1,6,1)=".",MID(A1,9,1)=".",MID(A1,12,1)=".",ISNUMBER(VALUE(LEFT(A1,5))),ISNUMBER(VALUE(mid(A1,7,2))),ISNUMBER(VALUE(mid(A1,10,2))),ISNUMBER(VALUE(mid(A1,13,2))))

I am sure there will be other options

 

[Toadstool]

Hi CBarryB,

This custom dta validation checks for the periods, that the first 5 characters are numeric and the last 8 characters are the date in dd.mm.yy format

=AND(MID(B2,6,1)=".",MID(B2,9,1)=".",MID(B2,12,1)=".",RIGHT(B2,8)=TEXT(TODAY(),"dd.mm.yy"),ISNUMBER(VALUE(LEFT(B2,5))))

Book1
	A	B	C
1
2		23456.02.06.20
3
Sheet1

 

[cbarryb]

thanks Guys, that works perfect for the 1 cell, but not the whole column. would I have to go through each cell and individually change the cell reference on each one? I have tried to change the cell ref from B2 to B:B and even the name of the column in the table, but I then get an error when typing it in. Maybe I will need to look at a UserForm for it instead.

 

[jimrward]

What happens if you select that cell and rest of column and select data validation you should be prompted to apply to whole selection

 

[sparky2205]

Copy B2
Select the range you want to paste to
On the Home tab select Paste>Paste Special>Validation (Radio Button)
The formula will automatically update its references

Note: avoid using full column references if at all possible i.e. B:B
Better to use B2:B10000 or as short as you can get away with

 

[sparky2205]

Nice one jimrward, I like that.

 

[Toadstool]

thanks Guys, that works perfect for the 1 cell, but not the whole column. would I have to go through each cell and individually change the cell reference on each one? I have tried to change the cell ref from B2 to B:B and even the name of the column in the table, but I then get an error when typing it in. Maybe I will need to look at a UserForm for it instead.

You can just copy & paste from the first Data Validation cell and relative addressing will take care of it for you:

 

[jimrward]

@Toadstool whilst your incorporation of today’s date into the formula is cool what happens when the date has moved on And an old data is revisited for whatever reason it will try and force the current date as the only valid entry

 

[Toadstool]

@Toadstool whilst your incorporation of today’s date into the formula is cool what happens when the date has moved on And an old data is revisited for whatever reason it will try and force the current date as the only valid entry

I don't know the use case and the OP stated "its the end of the account number first and then todays date at the end" so that's the way I formulated it.