Keeping series name in a fixed cell when changing data value cells

Got it.

The best (IMHO) answer is an intermediate table.

Charts can only take direct references, which is why you're having to update them every time your main table expands.

The trick is to create a separate table (can be on the same sheet or a different sheet, and hidden if you don't want to see it). This table is configured to capture the last 12 values from the main Credit Card table. Your chart then references this intermediate table rather than the main one.

In this setup, each time you add a new row (month) to your expenses sheet, the intermediate sheet updates with the last twelve values, and your chart follows suit.

To replicate this I created a simple 'Credit Card' table with two columns - Month, and Expense:

(your sheet may, of course, have additional columns)

Then I created a new table I called 'Rolling 12 Months' that extracts the last 12 items from the Credit Card table:

To do this, use the formula:

A2=INDEX(Credit Card::A,ROWS(Credit Card::$A)−11,1)

The INDEX() function takes three arguments - a range to search (in this case Column A in the Credit Card table), and a row and column index.

For the row I calculate the ROWS() in the Credit Card::$A table and subtract 11 (to get the eleventh-last entry).

For the column index I simply use 1

So if you have 4 years of credit card expenses (48 rows), the INDEX lookup the 37th entry (48 - 11) in that column.

As you add a new 49th month, the INDEX() will lookup the 38th entry (49-11)

Adjusting the numbers coming down, in cell A3 you subtract 10, A4 subtracts 9, etc.

The same principle is used for the Expense column, just substituting the column that has the expenses (Credit Card::B in my example).

Now you have a table that always shows the last 12 items on the main table.

From here it's simple to create your chart that references the Rolling 12 Months table, and it will always be up to date.

Chances are you have your chart references to a specific area, e.g.:

In this case you can see my chart series is based on cells B2:B13 on Table 1.

Instead, change this to capture the entire column (Click the Value cell, delete the 'Table 1::B2:B14', then click on the column header in the table. You should see something like:

Now the chart should auto-scale to include all the values in the entire column. As you add new rows (months), the chart will expand to include the new data.

Can you post a screenshot? shift-command-4, select area with crosshairs, release, start new post here and use the mountain-and-moon 'Image insertion' icon beneath the compose window to insert the screenshot from the Desktop. In the screenshot try to show the data references in the chart. I don't think you should have to change each one individually but it depends on your setup.

SG

Put the "name" in a header row. Either make row 2 a header row or move the name to cell E1 (and make row 1 a header row if it is not one already).

Making the row with the label a header row didn't solve the problem?

The stated problem was that the series name was changing when the chart data was adjusted by dragging downward. That will happen if the series name is in a data row but not if in a header row.

First screenshot is the table using the first few rows of data.

Second screenshot is after I dragged down on the blue box to edit the data references. The data adjusted, the series name stayed where it was.

> Making the row with the label a header row didn't solve the problem?

No because, as I read it, the OP's table has many rows, and he wants the chart to reflect the last 12 entries.

Using a header row would exclude the title from the chart data (or, more correctly, use it as the legend), but would either include all rows values (not the last 12), or require the OP to update the chart series reference (which is what they were doing). The intermediate table extracts the data that's needed.

I see what you mean

Agreed, it's easier to drag the bounding box than it is to reset the series reference in the chart settings. The intermediate table does it automatically, though, as new rows are added.