Data validation from list between start & end hour

**ElmerS** · 08-31-2013, 01:10 PM

Hi,

Assume two cells A1 & A2.

A1 has: 07:30
A2: has 11:00

In cell A3 I would like to define DV, from list (not typing) for every 15 minutes.

If I'm not clear enough I will emphasize - I DO NOT want to present the time intervals in a helper-column.

I came up with this Array-formula - which presents the correct intervals but the DV from list will not accept it as a List source.

=(ROW(INDIRECT(A1*96&":"&A2*96))*0.25)/24

An attempt to define a name to that formula and use that name as the DV List source ended up with an error.

Thanks for your efforts,

Elm

**Ace_XL** · 08-31-2013, 01:29 PM

This wont create a DV list but will do the job..

In Cell B1
DV-- Allow custom-- formula
=AND(MOD(B1*24,0.25)=0,B1>=$A$1,B1<=$A$2)
or
=AND(MOD(B1*24,0.25)=MOD($A$1*24,0.25),B1>=$A$1,B1<=$A$2)

**Tony Valko** · 08-31-2013, 04:03 PM

The source for a data validation list must be either a reference to a range of cells (which can be a formula that points to a range of cells) or a manually entered (or VBA generated) comma delimited list.