Dynamic Range Charts

[Steph12]

Hi Guys,

Ok so I need to create a dynamic chart that only portrays the last 12 sets of data.

The data goes along like this and continue with the dates and weeks

[TABLE="width: 492"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"]05/01/2013[/TD]
[TD="align: right"]12/01/2013[/TD]
[TD="align: right"]19/01/2013[/TD]
[TD="align: right"]26/01/2013[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Ashley[/TD]
[TD="align: right"]27.01%[/TD]
[TD="align: right"]25.34%[/TD]
[TD="align: right"]27.91%[/TD]
[TD="align: right"]31.89%[/TD]
[/TR]
[TR]
[TD]Tiffany[/TD]
[TD="align: right"]2.53%[/TD]
[TD="align: right"]1.76%[/TD]
[TD="align: right"]1.80%[/TD]
[TD="align: right"]1.37%[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD="align: right"]2.02%[/TD]
[TD="align: right"]1.39%[/TD]
[TD="align: right"]2.05%[/TD]
[TD="align: right"]1.45%[/TD]
[/TR]
[TR]
[TD]Steph[/TD]
[TD="align: right"]0.62%[/TD]
[TD="align: right"]0.50%[/TD]
[TD="align: right"]1.20%[/TD]
[TD]<0.000001%[/TD]
[/TR]
[TR]
[TD]Diana[/TD]
[TD="align: right"]1.01%[/TD]
[TD="align: right"]1.27%[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]1.16%[/TD]
[TD="align: right"]1.17%[/TD]
[TD="align: right"]1.10%[/TD]
[TD="align: right"]0.48%[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]6.76%[/TD]
[TD="align: right"]4.86%[/TD]
[TD="align: right"]4.54%[/TD]
[TD="align: right"]5.60%[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD="align: right"]7.92%[/TD]
[TD="align: right"]6.03%[/TD]
[TD="align: right"]5.65%[/TD]
[TD="align: right"]6.09%[/TD]
[/TR]
[TR]
[TD]Abby[/TD]
[TD="align: right"]5.61%[/TD]
[TD="align: right"]4.32%[/TD]
[TD="align: right"]4.97%[/TD]
[TD="align: right"]4.53%[/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD="align: right"]3.61%[/TD]
[TD="align: right"]4.18%[/TD]
[TD="align: right"]3.01%[/TD]
[TD="align: right"]2.34%[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD="align: right"]9.22%[/TD]
[TD="align: right"]8.50%[/TD]
[TD="align: right"]7.98%[/TD]
[TD="align: right"]6.88%[/TD]
[/TR]
[TR]
[TD]Emily[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Could you please tell me a step by step guide and a formula that will work for this.

Thanks

 

[DanMeakin]

Hi Steph,

Using the ‘Name Manager’ in the ‘Formulas’ tab you can create a named range based on an OFFSET function. Make sure you understand the basics of what an OFFSET is.
Your formula would always want to include the last 12 sets.


The formula would look something like this:

=OFFSET(reference, rows, columns, height, width)

=OFFSET(‘sheet 1’!$A$2, COUNTA(‘Sheet 1’!$A:$A)-12, 0,12,1)

This is telling excel to start at the top, travel down the rows until 12 from the bottom. Then it says to make the range 12 columns.
This formula is valid for where column A is the date range and A2 is the first date.

Once you have created this named range (called "Date" or something similar) you can create another range for values based on the date.

Your values formula would look something like this:

=OFFSET('sheet 1'!Date, 0, 2,,)

Hopefully this gives you a place to start. Do a little bit of reading because there are explanations far better than mine.


Cheers!