Data Validation Questions

TheSubject

New Member
Joined
Feb 16, 2016
Messages
11
Hi, I have a few data validation questions which I'm struggling with.

Firstly I am trying to make a bookings form, so am looking for a dropdown with every fifteen minutes between open and close. Now, Time as a data validation does not provide a drop down, so I am doing it based on a list of available times. Because a) our opening times vary across the week, and b) my dynamic list of times will exclude fully booked periods, I need the source of the list to vary in length to avoid a load of blanks. Basically - what the "ignore blanks" tick box is ASSUMED to do, before you quickly learn it doesn't. What is the work around to achieve this? I have tried googling and have copy and pasted a load of different options but once I've edited them from row to column and the example cells to mine etc, I just don't get the results.

Secondly, I am entering a box for the customer to enter an email address. I am still reading up on excel sending emails, but I want to ensure they type in a correct email address, i.e. to not allow them to submit without at least it having an @ sign in it! Again data validation: does contain an @ sign, and maybe a . Is this possible?

Thanks in advance for all your help,

G
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,805
Use sheet formulas to make a list of these 15 minute intervals on a sheet.
An example would be put the start time in A1, the end time in A2
In B1, put =A1
In B2, put =IF(B1>=$A$2,"",B1+TIME(0,15,0)) and drag down.

Then for the list source of your validation list =OFFSET(B1,0,0,COUNT(B:B),1)

That will have the list change depending on the A1 and A2 entries.
Controlling the black-out periods and changing start times is all done in the sheet (the formulas in column B)

Note that the format of the drop down list (9:00 vs. 9:00 AM) is controled by the formatting of the cells in column B, the format shown in the cell after an item is selected comes from that cell's number formatting.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,116
Messages
5,546,035
Members
410,721
Latest member
adi772
Top