Think I've sorted it by adding the COUNTA function into it...

=OFFSET(A1,0,MATCH(A2,A1:H1,0),1,MIN(5,COUNTA($1:$ 1)-MATCH(A2,A1:H1,0)))

Thanks again Stefi.

"Trevor Williams" wrote:

Hi Stefi, this is so close, thanks.
The row of years could go on and on whereas your formula is restricted to
the 11 years I've mentioned.

Is there a way to change it to use a variable year range?

Trevor

"Stefi" wrote:

Years being in A1:H1, start year in A2, use this formula in
DataValidationSource for end year:
=OFFSET(A1,0,MATCH(A2,A1:H1,0),1,MIN(5,8-MATCH(A2,A1:H1,0)))

Regards,
Stefi

€žTrevor Williams€� ezt Ã*rta:

Hi All

I'm trying (without success) to build a dynamic named range as
follows:

I have a row of variable sequential years
2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 | 2011

The user can select a 'start year' from a dropdown list created from
the years above.

I then need to create a dynamic range to only include years after the
start date, but up to a maximum of 5 years.

So, if the user selects the start year 2005, the the end year list
would be
2006 - 2010

However, if the user selects the start year 2009 the end year list
would only include
2010 - 2011 as 2011 is the last year available...

I've checked Debra Dalgleish's site regarding this type of thing but
just can't crack it!

Any help appreciated.

Trevor Williams