Leveraging Range Names in a Graphic

EE Pros,

If I create a dynamic list (horizontal row) of data points (X, Y1 and Y2) and I name a range around each original list/string, can I embed it in a graphic (line graph or bar graph) that will automatically change as the list grows or shrinks?  If so, can you describe how I accomplish this in the graphic options?

Thank you,

B.
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aflockhartCommented:
Yes, you can create the chart as normal, and then change the Data Sources to refer to the named ranges instead of to a range of cells.

When entring the ranges as the source of each series, you;ll probably need to include a full reference to the workbook name as well as the range name ( assuming the name has been created with workbook scope) - i.e. the source would be something  like:

=mybook.xlsx!my_x_range
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Yes you can use the Named Ranges as a source data for charts.
Follow these steps...

1) Create your Named Ranges.

2) Press Alt+F1 to insert a blank Chart.

3) Right click the Plot Area and choose Select Data.

4) Add the legend series and provide a Series Name and in the Series values Box use your named range with the Sheet reference i.e. if your named range is Year on Sheet1, input Sheet1!Year in the series values box and click OK.

5) Now Add the Horizontal Series in the same way.

So your chart will be dynamic to include the new values added in the source data.

Does this help?
aflockhartCommented:
Both comments give similar suggestions, different ways to approach it.  One difference to be aware of is in the definition of the named ranges that you use.  By default, named ranges are created with "workbook" scope, i.e. the same name can be used anywhere in the workbook and it will refer to the same original cells.  If you use this type of name, you need to use the workbook name as part of the reference when you use the name as the source of data.

There is another option when creating names, which is to set them up having "worksheet" scope - this allows the same name to be used on more than one sheet, referring to different cells in each case. If your names are set up like this, you need to use the worksheet name instead of the workbook name in the reference, as in sktneer's example.

Demo file attached with some dynamic names and a chart that is based on them.
demo-dynamic-charts.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Thanks Guys!   Appreciate each of the comments and thank you for the demonstration.....very helpful!  I'll be mocking up a version of what I'm trying to accomplish with Range Names and Graphics and then submitting another question when I get stuck.

Much thanks,

B.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.