Data validation lists help?

[RMcA]

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?

 

[JP]

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

 

[RMcA]

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

• - 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,

 

[JP]

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

Sorry I couldn't be more help here.


--JP

 

[T. Valko]

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()))))

 

[RMcA]

Thank you Biff! Works a treat.



Yours gratefully,

 

[T. Valko]

You're welcome. Thanks for the feedback!