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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,127
Messages
5,526,990
Members
409,733
Latest member
revender17

This Week's Hot Topics

Top