Excel 2007 dada validation

**DAT** · 03-16-2013, 07:47 AM

Hi,

I'm fairlynew to creating office spreadsheets, but can usually get by with google and looking at example templates.

However I can't find the answer to the following question (probably because I am not phrasing g it correctly).

I am need to filter my table by week date (eg show all rows for w/c 22 march.

I have achieved this by using data validation, drop down list option to a pre populated list of dates on the reference shhet. This works fine. The list is 52 rows long (each week of the year).

Is there a way of only showing the weeks greater than today in the drop down list? Weeks gone by will never be used.

I can use the conditional data by directly referencing date list and by usinf the named range method. Can't write vb though.

Can anyone help?

**DAT** · 03-16-2013, 07:48 AM

Apologies fot typos. On phone.

**samba_ravi** · 03-16-2013, 08:20 AM

post the sample sheet

**Palmetto** · 03-16-2013, 06:35 PM

See if you can adapt this approach.

Create a named formula for the position of the current week in the list

Name: Current_wk
Refers to: =MATCH(TODAY(),Sheet1!$A$1:$A$52,1)

Create a named range for the list of weeks that exclude previous week dates
Name: Weeklist
Refers to: =INDIRECT("Sheet1!$A$"&Current_wk&":$A$52")

Use Weeklist as the list source in the data validation

**martindwilson** · 03-16-2013, 07:40 PM

validation list can be
ist entry say in H1
=TODAY()-WEEKDAY(TODAY())+9
second entry filled down to week 52
=H1+7
this will give the next 52 weeks mondays

**NeedForExcel** · 03-17-2013, 01:07 AM

@martindwilson -

Hey!

Could you please stress a bit and Break Up the formula for my better understanding.

I am unable to conclude how your are able to get a Monday as the return value every time with the formula?

Thanking You,

Deep

**martindwilson** · 03-17-2013, 07:32 AM

TODAY()-WEEKDAY(TODAY()) will give the previous saturday try it then evaluate it,
monday 10th -2 say would give sat 8th ,
wed 10th -4 gives sat 6th
add 2 to get monday add 9 to get following monday week