Solved

# MS Excel Graph with dynamic range

Posted on 2011-10-17
181 Views
Hi guys -

I'm not sure the 'right' wording for what I'm trying to do so I'll try to explain it and see if you can help me.

I have a table of data. In this table I have two series of values that I want to graph (Column A).

But everyday, I'm going to be pasting more data in and having a static range that i set my series on my chart from won't work. So what I'm trying to do is a..

SELECT the series of my worksheet, where the name of my series equals the ProjectName in column A.

A VLOOKUP of sorts?

Or the only other way I can explain..is like when I do a COUNTIFS(RANGE, Criteria...) where "Criteria" would be my value in A.

Is this possible to do in charts so I don't have to specify each range statically?

Thanks so much!
0
Question by:rmm2001

LVL 41

Expert Comment

YES!  You can create a dynamic range from which you can construct your charts.

Here are a couple links to tips:

http://www.contextures.com/xlnames02.html

http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/

After having a read, if you want more assistance, please upload a mock workbook and I'll step you through it with that example.

Cheers,

Dave
0

LVL 41

Expert Comment

Here's a simple example dynamic chart.  Go to the name manager (Ribbon - formulas->names) and look at the two named ranges created - names and values.  Those are the key dynamic ranges that the chart is built on.  This follows the chandoo link example, I posted in my tip, above.

See attached,

Dave
dynamicChart-r1.xls
0

LVL 7

Author Comment

I added on sheet 2 what I'm trying to do a little. There will be more records added to the table, so the formula, of sorts, needs to take that into account. But where each column is a different series in the chart, group by the names in column A.

Thanks so much for looking into this with me :) Copy-of-dynamicChart-r1.xls
0

LVL 41

Expert Comment

Ok - you'll need to create a dynamic range for each of the dimensions:  Name, Date, Age, and Age2.

Take a look at those in the attached workbook.
dynamicChart-r2.xls
0

LVL 41

Accepted Solution

Or, if you're needing to breakout Smith, Jones, Age, and Age2, it gets alot more complicated.  I would suggest considering Pivot Table/Pivot Chart

Take a look at the example, and play with it.  If you like it, you can also link the pivot table data to a dynamic range.

See attached

Dave
dynamicChart-r3.xls
0

LVL 41

Expert Comment

One more example that gets close to what you posted using pivot table.

Cheers,

Dave
dynamicChart-r3.xls
0

LVL 7

Author Comment

That kinda makes sense...

The thing it's not doing though is making two series..so one for JonesAge, JonesAge2, SmithAge, SmithAge2.

Ideally, I'd want to put a filter on the table A1-D11, and say filter where Age is between 30 and 50 ,and then have the chart reflect that filter and adjust just to show those values that fall between that filter.

Thanks again!
0

LVL 41

Expert Comment

Actually, the last pivot table is making those series.

Also, with that pivot table, you can add Age to the report filter, and filter accordingly.

How about adding a bit more data and see how the pivot table takes shape?

Dave
0

LVL 7

Author Comment

Hmmm let me play with this - it could work - I just have the problem that my users don't know how to use pivot tables (just filtering tables) ... (stay tuned).

Thanks!
0

LVL 41

Expert Comment

Ok - doing it without pivottables is possible, it would need helper columns (to create the name/age combinations that would be used- basically each line on the chart will need one column) and a lot of range names to be defined (one for each series).

Dave
0

LVL 7

Author Closing Comment

PivotChart was perfect!!! It'll take some getting used to but much easier than the alternatives.

Thanks!
0

## Featured Post

### Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.