Data Validation Questions

[TheSubject]

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

 

[mikerickson]

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.