Data Validation Questions


New Member
Feb 16, 2016
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,


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


MrExcel MVP
Jan 15, 2007
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

Latest member