|
Dynamic Range Setup...
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 |
Dynamic Range Setup...
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 |
Dynamic Range Setup...
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) -- __________________________________ HTH Bob "Trevor Williams" wrote in message ... 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 |
Dynamic Range Setup...
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 |
Dynamic Range Setup...
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 "Bob Phillips" wrote: 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) -- __________________________________ HTH Bob "Trevor Williams" wrote in message ... 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 |
Dynamic Range Setup...
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? "Bob Phillips" wrote: 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) -- __________________________________ HTH Bob "Trevor Williams" wrote in message ... 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 |
Dynamic Range Setup...
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 |
Dynamic Range Setup...
D'oh -- try this one...
=OFFSET(A1,0,MATCH(A2,$1:$1,0),1,MIN(5,COUNTA($1:$ 1)-MATCH(A2,$1:$1,0))) "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 |
| All times are GMT +1. The time now is 08:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com