Dynamic Range Setup...

[Trevor Williams]

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

 

[Stefi]

Years being in A1:H1, start year in A2, use this formula in
Data>Validation>Source 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:

 

[Bob Phillips]

What are you going to do with this range?

Assuming the years are in B1:J1, and the dropdown is in A2, try

=INDEX(INDEX($B$1:$J$1,MATCH($A$2,$B$1:$J$1,0)):$J$1,0)

 

[Don Guillett]

Where h1 has 2004 and h3 is your validation list
=MIN($H$3+5,$H$1)

=$H$3&":"&MIN($H$3+5,$H$1)

 

[Trevor Williams]

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

 

[Trevor Williams]

Hi Bob - thanks for the response.

The idea of this range is to restrict the user in selecting an end year that
is a max of 5 years from the start year. This would be chosen from an End
Year dropdown list populated with the dynamic range.

Your formula lists ALL the years post the chosen Start Year. Can it be
restricted to list 5 years only, or the number of years post start year if <
5? (i.e. if yrs are 2009 - 2014 and start yr is 2010 the list would show
2011,2012,2013,2014 (not 5 years))

Think that makes sense(?)

Trevor Williams

 

[Trevor Williams]

Also Bob, the year row is not resticted to the years I've supplied, and could
go way beyond $J$1... is there a way to build that variable in?

 

[Trevor Williams]

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]

D'oh -- try this one...

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