Thread: Data Validation dependig on 1st. of the month

Hi,

Assume A1: 01/03/2010 (dd/mm/yyyy)

If in B1: =ROW(INDIRECT(A1&":"&EOMONTH(A1,0))) it will produce an array like:

{40238;40239;40240;40241;40242;40243;40244;40245;40246;40247;40248;40249;40250;40251;40252;40253;402 54;40255;40256;40257;40258;40259;40260;40261;40262;40263;40264;40265;40266;40267;40268}

I would like to declare C1 as 'DV' to enable the user to select one out of 31 dates - but 'Excel' rejects all my trials.

I even tried to name the formula as MNTH and to deliver that name to the DV - but Excel rejects it as well.

Is there a workaround ? (I only want to use one cell with the 1st. of month)

Thanks, Elm

You list that you are using 2 versions of Excel...

XL2003 doesn't allow use of EOMONTH() in data validation because it is an analysis toolpak addin function... in XL2007, it is part of the regular functions...


so in 2007, try custom formula:

=MATCH(C1,ROW(INDIRECT(A1&":"&EOMONTH(A1,0))))

in 2003, try custom formula:

=MATCH(C1,ROW(INDIRECT(A1&":"&DATE(YEAR(A1),MONTH(A1)+0+1,0))))

Thanks, NBVC,

Tried them both - in "2003" & "2007" but upon declaring the DV, all I get is the same error as before.

Would you be so kind to attach a WB (xlsx) with both(!) options.

PS: I hope I made myself clear that I want to SELECT one of the 31 dates and not to restrict typing.
Elm

See attached...

Enter dates in C1, if not within your range, it gives error...

Notice... I found a small error in my formulas.. forgot the 0 argument in the MATCH() formulas...

Thanks, again, but no - this is not what I'm looking for.

Maybe the attached picture will clarify my request.

The picture shows the common/known way.
I declared a range of 31 cells with all the dates and the VD is referring to that range.
I want to have only one cell holding the 1st. of month and to be able to get the same DV in C1.

Elm

I think OP's aim is for C1 to be a DV List of dates from 1st to last day of Month specified in A1 ...

I've not found a way of doing this - ie creating the list entirely in memory (for want of a better phrase) won't work.

Even though the below:

Name: _dates
RefersTo: =TEXT(ROW(INDEX($A:$A,$A$1):INDEX($A:$A,DATE(YEAR($A$1),MONTH($A$1)+1,0))),"dd-mmm-yy")

will generate 31 values, ie:

=ROWS(_dates) will return 31

and

=INDEX(_dates,ROWS(A$1:A1)) copied down will list the values

the name itself can not be used as source for DV list... I for one have no idea if there's a way of using it.

I apologize for having misinterpreted the question....

Originally Posted by ElmerS

Thanks, DonkeyOte,

This also does not answer my precise request/question.

Elm

Doesn't it ? Sorry, I thought the below assertion:

Originally Posted by D.O

...creating the list entirely in memory (for want of a better phrase) won't work.

related to your question entirely.

See, this is why I vowed not to enter your threads as I invariably fail to meet your exacting standards for precision...

As DO mentioned, not sure if you can do that in one single data validation statement..

Try this,

In a Cell, say G1, enter =A1, then in A2, =IF(G1="","",IF(MONTH(G1+1)<>MONTH(G1),"",G1+1)) copied down to G31

Then create a named range called: Dates

with formula: =OFFSET(Sheet1!$G$1,0,0,COUNTA(Sheet1!$G$1:$G$31)-COUNTBLANK(Sheet1!$G$1:$G$31),1)

Then, in C1: Data|Validation|List =Dates

You're asking for trouble with that one NBVC....

Sorry to say but you lost me on that one I'm afraid - again not sure if that's down to my interpretation of your narrative or that the narrative doesn't make sense - probably the former as usual.

Just to clarify though... I don't actually recall stating anywhere that you should use multiple helper cells (I know better by now).

If you read my post you will note that

a) I was using the additional cells to illustrate that using a Defined Name approach to store the array we can see that the Name itself populates as expected by listing the results.

b) I was simply stating that further to the above I was not aware of any way in which a named range populated entirely "in memory" could be used as the source for a Validation list.
as usual I would add the caveat that the above is not mean to be read as impossible.

(by "in memory" I simply mean where not referenced to any physical values be they contained in a range or named constants etc)

I concentrated on the Defined Name approach as I had presumed you had realised that storing the formula you had in a single cell was not really going to get you anywhere
(a single cell will only ever contain a single value as far as Excel is concerned - irrespective of whether the formula within is returning an array of values or not)

At least not that I myself am aware of... but if it can be done I would like to see it myself so if you find something elsewhere in the meantime please post it here also.