Excel: Dynamic Chart Ranges


I'm seeking your help on creating a dynamic range for my Excel 2010 chart (attached).

In the yellow cell (B2), is an integer user field. I have tried to link this to an offset formula that calculated a charts x & y axis (see green cells).

I would like the charts range to update whenever a user edits B2. I tried creating a named reference for the chart series, but it didn't work.

Can you tell me what I'm doing wrong?
Who is Participating?
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.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

you need to place the Offset formula into named formulas (or range names) and then use these in the chart.  You need one range name for the X axis labels and one for the data series.
May I suggest that you add another cell where the user enters a starting year? Let B3 have that value as an integer.

Now create two range names with Formulas ribbon > Name Manager > New

chtXLabels refers to =OFFSET(Sheet1!$D$6,Sheet1!$B$3,0,Sheet1!$B$2,1)
chtSeries1 refers to =OFFSET(chtXLabels,0,1)

Now edit the chart series. In the Series Values enter

Edit the Horizontal Axis Labels values and enter

You must precede the named range with the sheet name or the file name when you use range names in charts.

See attached file

cheers, teylyn

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
Glenn RayExcel VBA DeveloperCommented:
Sounds like you had the right idea about creating a named range, but you just needed to build a new data series with that name, rather than trying to modify an existing data series.

I created a named range called "ChartRange" using the following formula:

Then I added a new data series and used this specific reference for the series values
(Note inclusion of "Sheet1!" in the reference; that is key).

I've attached a modified version of your file.

dabug80Author Commented:
Thanks for also providing the dynamic range change
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.

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.