Solved

# Excel: Dynamic Chart Ranges

Posted on 2014-08-13
347 Views
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
Question by:dabug80

LVL 50

Accepted Solution

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

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

Thanks for also providing the dynamic range change
0

## Featured Post

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.