Create Dynamic graph

[welchs101]

I have a sheet of data (see enclosed).

I have created dynamic graphs before but this graph is a little different. In my other graphs that i have done the dynamic variable is the x-axis.......like if i were adding a new month. But in this one I am adding a new month but the month is not the x-variable (see enclosed file). As such i am not sure how to make this graph dynamic.

In this graph the x-variables are static........

Any ideas?

[Roger Govier]

I have a sheet of data (see enclosed).

There is nothing attached to this post.

[welchs101]

sorry, i thought i uploaded it but i guess i did not........sorry about that.

[Andy Pope]

Turn your data in to a table.
Create a chart based on the table
Switch chart row/columns data

[welchs101]

what do you mean by turn it into a table..........fyi: i have excel 2007

[Andy Pope]

Select range A1:F4
use ribbon Insert > Tables > Table

The use Insert > Charts > Column chart.
Use Chart Tools > Design > Data > Switch Row/Column

The chart will now automatically expand in series when rows are added.

[ChemistB]

Assuming your data header begins in row 1 column A like your example, set up a LstRow Defined name as follows
LstRow = COUNTA($A:$A) (this assumes no blank rows in column A)

Assuming you want to chart the last 12 rows of Column B
GraphRng1 = INDEX($B:$B,LstRow-11):INDEX($B:$B,LstRow)
Xaxis = INDEX($A:$A,LstRow-11):INDEX($A:$A,LstRow)

Does that work for you?

[welchs101]

ok, where do i put these named ranges so that adding another month would plot automatically?

[welchs101]

no it does not appear to work. the x-axis should be the values in row1.....B1, C1, D1, E1, F1.

For example: For the 1A: I should see the last 12 months of data for 1A. Similarly, for 2B i should see the last 12 months for 2B.

Its a weird graph.........but this is what i have to plot.

Does this make sense?

[Andy Pope]

If you create a chart table based on the last 12 entries in your expanding data table you only need define 1 named range for the source data.

The chart can be made on the static range.

[welchs101]

hummm, let me take a look at that. wont be able to get to it for about 1hr..........

THanks again for all the help.

[ChemistB]

I did end up creating 12 defined names for the 12 series. I think Andy's technique is much better for a number of reasons but, if you want to see how it was done, see attached. For the Series names/legend, I used this formula dragged down 12 rows

=TEXT(INDEX($A:$A,LastRow-ROW(A1)+1),"mmm' yy")

Reference those cells for the series names when adding series to the chart. Typically, I would hide these cells or put them underneath the graph.

[ChemistB]

FYI, you needed to expand your range to J1:O13 on your graph. The graph will ignore all the series where the x value is N/A so there is not an issue there. Hope that helps.