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.

[welchs101]

cool! I did not know about that.

But, i forgot to mention that i only need the last 12 entries plotted. This technique wont do that but i can use this method for other things. any idea how to dynamically plot THIS graph using only the last 12 entries.

thanks.

[Andy Pope]

Your example currently has 3 items. If your real data already has 12 or more items then you can use Name ranges or a separate table for charting that uses the INDEX formula to build a table of the last 12 rows.

[welchs101]

hi,

yes, i have more than 12 entries........i did not think about including all of them when i was making the example file.

I did include the "example" formulas for named ranges that i found on this site but they dotn work because of how my data is organized.

anyhelp would be appreciated.l

[welchs101]

if i need to provide any additional information please let me know.

[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.

[welchs101]

andy, i like your idea. I tried it out. I like it. Basically, i just created a 2nd set of data that only contains the last 12 entries. I did have a few questions that i am hoping to get answers for.

Note: The file contains what i think is really cool way of addressing the last 12 entries.........i cant claim credit for this at all i got the this from someone else here on the forum.......great place to learn new things.

I am enclosing a file which contains the suggestions from previous posters.


Question:
1) If i dont have 12 entries is there a way to make the graph not plot all 12 rows. Currently, if there are no entries for all 12 rows the graph still plots all 12 rows. I know i could use the "table" feature that andy spoke of and i will if there is no other soln. Just seems to me that there is probably a simple soln that i just dont know about.

[Andy Pope]

You can use autofilter to hide the NA entries.

[welchs101]

to use the auto filter..............i have to do this manually......right? I am trying to figure out a way to do it so i dont need to do anything manually.

[welchs101]

so i looked at some of the files you guys sent and i came up with this formula

OFFSET(Sheet1!$J$1,0,0,COUNT(Sheet1!$J:$J)+1,6)

I defined a name "mydata1" and set the above formula equal to mydata1. i used "select data" and then went into "chart data range" and set it equal to

='Sheet1'!mydata1

and it works some of the time but not all of the time.

is this the right approach? am i doing something wrong? is there a better way?

[welchs101]

anyone got any ideas?

[welchs101]

turns out they dont need the last 12 entries but rather onlyl the last 4..........so i think i am done with this thread.

thanks to all who helped..........andy and chemistb.

[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.