[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel: Dynamic Chart Ranges

Posted on 2014-08-13
3
Medium Priority
?
361 Views
Last Modified: 2014-08-14
Hello,

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?
Offset-Chart.xlsx
0
Comment
Question by:dabug80
3 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 40259877
Hello,

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
=Sheet1!chtSeries1

Edit the Horizontal Axis Labels values and enter
=Sheet1!chtXLabels

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
Offset-ChartDynamic.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40259894
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:
=OFFSET(Sheet1!$E$5,1,0,Sheet1!$B$2,1)

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

I've attached a modified version of your file.

Regards,
-Glenn
EE-Offset-Chart.xlsx
0
 
LVL 1

Author Closing Comment

by:dabug80
ID: 40260018
Thanks for also providing the dynamic range change
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question