MrExcel Publishing
Your One Stop for Excel Tips & Solutions

dynamic chart ranges


Posted by Al on January 06, 2002 3:28 PM

I'm trying to create a dynamic line graph of a range of values in a list. The initial list in col.E has 28 rows of values from E2-E29 (header in E1). The challenge is that a new value gets added to the bottom of the list each day but I only want the most recent 28 values plotted, dynamically of course. To illustrate, for Day1, I want to plot E2-E29. For Day2, I want to plot E3-E30, and so on.
The formula: =ADDRESS(COUNTA($E:$E),5)
properly gives me the address I want at the bottom of the list, e.g. E29 when it's Day1, E30 when it's Day2, etc. Then I tried creating a dynamic named range using OFFSET(ADDRESS(COUNTA($E:$E),5),-27,0,28,1) but
I get an error. My ultimate objective is to use this dynamic named range for the 'values' parameter in the SERIES function which actually plots my line.
This is for a "rolling 28-day" line chart.
Thank you very much for any response.
Al


Posted by Ivan F Moala on January 06, 2002 9:15 PM

Try entering this formula for your named range

=OFFSET(Sheet2!$E$1,COUNTA(Sheet2!$E:$E)-28,0,28,1)


Ivan