Validation numbers and a word

master_of_none

Board Regular
Joined
Jan 29, 2003
Messages
62
Is there a formula for the entire column that I can use for validation? I would like to have validation between 0000 and 2359 and to include one word. I know I can make a list but I was seeing if a formula or vba might work. XL2003

Thanks

Rob
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Not sure I understand what you mean...But I think this is what you want...

In A1 put

="OneWord " & TEXT(ROWS(A$1:A1),"0000")

Then copy/fill it down to A2359
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
I would like to have validation between 0000 and 2359 and to include one word.
Hello Rob,

Are the numbers time values, do you mean to say 00:00 and 23:59 or do you want them without the colon? If it's the latter would 2299 be allowed, I presume not.....
 

master_of_none

Board Regular
Joined
Jan 29, 2003
Messages
62
Barry, that's a good point. I wasn't thinking clearly. I have column B formatted so the users can type in just the time without the ":". I currently have validation in that column of whole number 0000 to 2359.

I need to set up validation for time values of 0000 to 2359 and the word "CNX".

Should I just make a list or is there some other way of doing it?

Rob
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
You could use this formula under the "custom" option

=IF(B1="CNX",TRUE,TEXT(B1,"00\:00")+0<1)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,842
Messages
5,507,618
Members
408,641
Latest member
billigee

This Week's Hot Topics

Top