Excel chart dynamic range-selection

Question

I have a client that has a simple yet complicated request for an excel sheet setup, and I can't for the world thing of where to start. I'm drawing a blank.

We have a data range. Example:

Quarter     Data
2010Q1       1
2010Q2       3
2010Q3       4
2010Q4       1

I have a chart built on top of that. Change data, chart changes, protect worksheet to keep other idi... er... users from changing old data. Simple.

What I want to have happen: When I add the next Q1 below Q4, the chart "automagically" selects the most recent 4Q. So when I update the data to:

Quarter     Data
2010Q1       1
2010Q2       3
2010Q3       4
2010Q4       1
2011Q1       7

The chart will show data for the last 4 quarters (2010Q2 thru 2011Q1). The goal being: keep "old" data on the same sheet, but have the charts update to most recent quarters.

I'm thinking: "fixed" data locations, reverse the data (new data at top), and just insert row each new quarter:

Quarter     Data
2011Q2       9
2011Q1       7
2010Q4       1
2010Q3       4
2010Q2       3
2010Q1       1

But this will involve a lot of changes to the already existing excel sheets and I was hoping that there may be an easier/better "fix".

---

EDIT: @Lance Roberts ~ Running with your suggestion:
- Little more detail... The data is setup such that the column information is in A, but data for multiple tables are in B+. Table 1 is B/C. Table2 is D/E. Etc.
- Data is also on a different sheet than the tables.

Going by: This Offset Description, what I've tried doing is adjusting similar to such:

NAME       FORMULA OFFSET(range, rows, columns, height, width )
DATA0      =OFFSET('DATASHEET'!$A$2, COUNTA('DATASHEET'!$A:$A - 8, 0, 8, 1)
DATA1      =OFFSET('DATASHEET'!$A$2, COUNTA('DATASHEET'!$A:$A - 8, 1, 8, 1)
DATA2      =OFFSET('DATASHEET'!$A$2, COUNTA('DATASHEET'!$A:$A - 8, 2, 8, 1)

Goal being to tie the length/location for B/C/etc data to A. So if I add a column on A, stuff tied to Data1/2 adjust accordingly (or 3/4/5/etc, which are different charts on different sheets )

I want data cells to be picked by the first row, and then an offset number to get data x columns over. Variations on the formula don't seem to be working.

1 issue I haven't solved yet: the data is not aligning properly:

"Data" is always, last column under 2nd to last Quarter. Last quarter is always empty. Data is shifting to the right (in this example, under 3Q10 - NOT under the correct column. 11 should be under 4Q10. 9.5 should be under 2Q10).

I know I'm getting something simple wrong...

---

Seems to be working. First thing I had to change was CountA - 9 (not CountA - 8). Next was the "column offset" (0, 1, 2, 3,...). Also split some stuff up to make it more compartmentalized (I do have to train someone else how to do this for her reporting needs).

Thanks Lance :)

Answer 1

If the chart is on the same sheet as the data:

Name the first cell of the data (A2) as a named range, say TESTRANGE. Created a named range MYDATA as the following formula:

=OFFSET(TESTRANGE, COUNTA($A:$A) - 5, 0, 4, 2)

Now, go to the SERIES tab of the chart SOURCE DATA dialog, and change your VALUES statement to:

=Sheet1!MYDATA

Now everytime you add a new row, it'll change the chart.