Help?

  • Thread starter Thread starter Tekkie
  • Start date Start date
T

Tekkie

Ok this is for all the formulae gurus out there.

TIA

I have a list in a particular column, lets say column A
This is a list of dates

I have another cell which uses data validation to reference this list. Say
B1

In cell B1 the only acceptable entries are that which are in column A

Can anyone supply me with a formula for my data validation which will only
allow 8 entries.

These 8 entries....appearing in the list in B1 will be 8 weeks in advance of
the current date.

I am currently using the following formula
=OFFSET(O2,MATCH(YEAR(NOW()),YEAR(O2:O200),0)-1,,SUMPRODUCT(--(YEAR(O2:O200)=YEAR(NOW()))))

To display only the current year.
I would like to reduce this to only 8 entries but I am unsure of how to
accomplish this.

Many thanks
 
I'm a little confused.

You say you have dates in column A that is the source for the drop down but
your formula references column O. I guss the mention of column A is just a
typo?

I'm still not sure what you want. Limit the drop down selections to 8? Well,
which 8?

My best guess is to limit the result of the height argument. Like this:

MIN(8,SUMPRODUCT(--(YEAR(O2:O200)=YEAR(NOW()))))

That assumes SUMPRODUCT is always >=8
 
Hi,

If I understand correctly you want today's date in A1 and then 7 additional
dates below it each a week into the furture. If so
In A1 enter
=TODAY()
In A2 enter
=A1+7
Copy this down to row 8.

In cell B1 set up the following:
Choose Data, Validation
Choose List from the Allows drop down
In the Source box Highlight the range A1:A8.

If this helps, please click the Yes button.
 
Shane

You are using some web frontend for this. What is the url ?

Tia. Best wishes Harald
 
Back
Top