I have a cell which has data validation applied.

It is validated to only allow a certain range of dates, in this case every
Friday this year. This range is manually set at the beginning of the year so
that users can only see this years dates.

My question is:

Is there some way I can add in a formula to the data validation source part
to automatically adjust the values that are shown in the drop down list?

What I need is a validation formula for the list which would show all the
dates that ended with 2008.

Does that make sense?

Anybody have a solution to this?


--
RMcA

For the cheats among us
http://www.gamefaqs.com/

Do you want an automatically adjusting range name so you can add dates
to the list?

If so, consider a dynamic named range. For example, range A1:A10 has
your list of dates. Create a name called "DateList' by pressing Ctrl-
F3 and in the "Refers To" box, put "=OFFSET($A$1,0,0,COUNTA($A:$A),1)
" (without quotes of course).

In your data validation in that cell, change it to "Allow: List" and
in the Source box, type "=DateList" (without quotes).

Now you can add entries to column A and they will automatically be
added to the data validation dropdown.


HTH,
JP

On Jan 28, 10:03*am, "RMcA" wrote:
I have a cell which has data validation applied.

It is validated to only allow a certain range of dates, in this case every
Friday this year. This range is manually set at the beginning of the year so
that users can only see this years dates.

My question is:

Is there some way I can add in a formula to the data validation source part
to automatically adjust the values that are shown in the drop down list?

What I need is a validation formula for the list which would show all the
dates that ended with 2008.

Does that make sense?

Anybody have a solution to this?

--
RMcA

For the cheats among ushttp://www.gamefaqs.com/

"JP" wrote in message
...
Do you want an automatically adjusting range name so you can add dates
to the list?

If so, consider a dynamic named range. For example, range A1:A10 has
your list of dates. Create a name called "DateList' by pressing Ctrl-
F3 and in the "Refers To" box, put "=OFFSET($A$1,0,0,COUNTA($A:$A),1)
" (without quotes of course).

In your data validation in that cell, change it to "Allow: List" and
in the Source box, type "=DateList" (without quotes).

Now you can add entries to column A and they will automatically be
added to the data validation dropdown.


HTH,
JP


JP Thanks for your reply.

That is not exactly what I was trying to do but you have inadvertently
provided me a solution to another problem :) So ta!

My problem is simpler than that, and I am no whizz with excel so I apologise
for the confusing explanation.

My list of dates, in date format dd/mm/yyyy is in column O, they are in
ascending chronological order, and are a list of dates of every Friday for
the next umpteen years.

My current data validation formula is a simple[list] - source =$O$2:$O$53

Where 02 is the first Friday of the year 2008 and O53 is the last Friday of
the year.
Next year this range will be manually altered so that the source formula
=$O$54:$O105

What I am trying to achieve is for the drop down list to be validated to
only show dates in column O, and only dates which are in the current year.
Which will in turn minimize the length of the drop down list.

The list of dates in column O is static and do not need to change.

I am trying to do away with the need for manually updating the source.
I am certain that for someone who actually knows what they are doing that
this is extremely simple.
Unfortunately I am not that person.

Doh!
It does seem that a dynamic named range is needed though I am unsure as to
how to achieve it.
Many thanks,


--
RMcA

For the cheats among us
http://www.gamefaqs.com/

Best I can suggest is to simply redefine the range to the current
year.

Sorry I couldn't be more help here.


--JP

On Jan 28, 11:55*am, "RMcA" wrote:

JP Thanks for your reply.

That is not exactly what I was trying to do but you have inadvertently
provided me a solution to another problem :) So ta!

My problem is simpler than that, and I am no whizz with excel so I apologise
for the confusing explanation.

My list of dates, in date format dd/mm/yyyy is in column O, they are in
ascending chronological order, and are a list of dates of every Friday for
the next umpteen years.

My current data validation formula is a simple[list] - source =$O$2:$O$53

Where 02 is the first Friday of the year 2008 and O53 is the last Friday of
the year.
Next year this range will be manually altered so that the source formula
=$O$54:$O105

What I am trying to achieve is for the drop down list to be validated to
only show dates in column O, and only dates which are in the current year.
Which will in turn minimize the length of the drop down list.

The list of dates in column O is static and do not need to change.

I am trying to do away with the need for manually updating the source.
I am certain that for someone who actually knows what they are doing that
this is extremely simple.
Unfortunately I am not that person.

Doh!
It does seem that a dynamic named range is needed though I am unsure as to
how to achieve it.
Many thanks,

Assume your list of Friday dates is in the range I1:I200

As the source for your drop down list use this formula:

=OFFSET(I1,MATCH(YEAR(NOW()),YEAR(I1:I200),0)-1,,SUMPRODUCT(--(YEAR(I1:I200)=YEAR(NOW()))))

--
Biff
Microsoft Excel MVP


"RMcA" wrote in message
.uk...
I have a cell which has data validation applied.

It is validated to only allow a certain range of dates, in this case every
Friday this year. This range is manually set at the beginning of the year
so that users can only see this years dates.

My question is:

Is there some way I can add in a formula to the data validation source
part to automatically adjust the values that are shown in the drop down
list?

What I need is a validation formula for the list which would show all the
dates that ended with 2008.

Does that make sense?

Anybody have a solution to this?


--
RMcA

For the cheats among us
http://www.gamefaqs.com/

Thank you Biff! Works a treat.

:) :) :)

Yours gratefully,


--
RMcA

For the cheats among us
http://www.gamefaqs.com/

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"RMcA" wrote in message
.uk...
Thank you Biff! Works a treat.

:) :) :)

Yours gratefully,


--
RMcA

For the cheats among us
http://www.gamefaqs.com/