MS Excel Graph with dynamic range

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!
LVL 7
rmm2001Asked:
Who is Participating?
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
rmm2001Author Commented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
One more example that gets close to what you posted using pivot table.

Cheers,

Dave
dynamicChart-r3.xls
0
 
rmm2001Author Commented:
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
 
dlmilleCommented:
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
 
rmm2001Author Commented:
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
 
dlmilleCommented:
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
 
rmm2001Author Commented:
PivotChart was perfect!!! It'll take some getting used to but much easier than the alternatives.

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.