34

I'm trying to create a chart with a range built dynamically using the INDIRECT function. Excel does recognize the range I am creating using INDIRECT as it highlights the corresponding range on the sheet:

enter image description here

However when saving the chart, I get an error message saying the function is not valid:

enter image description here

Does anybody know what the problem is / how to create a dynamic chart range from a specific start to specific end point?

PS: You can download the above spreadsheet here. The formula I was using:
=INDIRECT("sheet!"&E2&":"&E3)

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
Max
  • 12,794
  • 30
  • 90
  • 142
  • 1
    I think it doesn't take the function `INDIRECT` :( If you try to use `=INDIRECT("Sheet!$A$3:$B$7")` this too throws an error. Waiting to see if someone else's got some workaround! – Jerry Aug 16 '13 at 11:59
  • It seems like it. What's frustrating is that it does highlight the proper range. – Max Aug 16 '13 at 12:02
  • Are you intending to use the range for the chart? You can't use `INDIRECT` to create a "range" of cells that can be used this way. – Martin Aug 16 '13 at 12:02
  • Yes, the idea is to create a range for the chart. Whether I need to use `INDIRECT` or another function in the end doesn't matter to me as long as I can define start/end of range like in the example. – Max Aug 16 '13 at 12:48
  • 2
    Actually in Excel you can use the indirect method in the name manager. So you create a named range with the =INDIRECT("sheet!"&E2&":"&E3) part and then use that name in the chart. Unfortunately this doesn't work with Google Docs, so is "Excel only". – wcndave Oct 10 '17 at 13:13

7 Answers7

30

The way you are trying to do it is not possible. Chart data range has to have a fixed address.

There is a way around this, and that's using named ranges

Put the number of rows you want in your data in a cell (e.g., E1)
So, using your example, I put Number of Rows in D1 and 6 in E1

In name manager, define the names for your data and titles
I used xrange and yrange, and defined them as:

xrange: =OFFSET(Sheet1!$A$2,0,0,Sheet1!$E$1)
yrange: =OFFSET(Sheet1!$B$2,0,0,Sheet1!$E$1)

now, to your chart - you need to know the name of the workbook (once you have it set up, Excel's function of tracking changes will make sure the reference remains correct, regardless of any rename)

Leave the Chart data range blank
for the Legend Entries (Series), enter the title as usual, and then the name you defined for the data (note that the workbook name is required for using named ranges)
data points

for the Horizontal (Category) Axis Labels, enter the name you defined for the labels
data labels

now, by changing the number in E1, you will see the chart change:
6 in E14 in E1

SeanC
  • 15,695
  • 5
  • 45
  • 66
  • 2
    Nice one. I thought I knew how to do this, but it took me a long time to remember! The main gotcha I found was that you can't just use named ranges, you have to qualify with the workbook or sheet name. – Doug Glancy Aug 16 '13 at 14:50
  • Thx. In your example, the series end was dynamic (based on number of rows in `$E$1`, but the start wasn't. I adapted `Doug Glancy's` example: let's say I define the start day in cell `$E$2`, then `yrange` becomes `=OFFSET(INDIRECT("Sheet1!$A$"&MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)),0,0,Sheet1!$E$1)`. – Max Aug 19 '13 at 08:48
  • 1
    you can have somewhere that defines # lines to skip, and the range would become `=OFFSET(Sheet1!$A$1,LinesToSkip,0,LinesOfData)` - Offset takes 2-4 parameters after the starting location- 1 and 2 are the rows and columns to move from the start, and 3 and 4 are the number of rows and columns (default is 1) to return – SeanC Aug 19 '13 at 12:50
  • note that with this approach, your workbook's name cannot contain spaces. unless there's a workaround for that which i'm unaware of. – szaman Jun 04 '16 at 10:08
  • @foerno, the work around is to put the sheet name in single quotes. e.g. `='My Data.xlsx'!x_axis` – SeanC Jun 04 '16 at 13:35
19

Mine is similar to Sean's excellent answer, but allows a start and end day. First create two named ranges that use Index/Match formulas to pick the begin and end days based on E2 and E3:

rngDay

=INDEX(Sheet1!$A:$A,MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)):INDEX(Sheet1!$A:$A,MATCH(Sheet1!$E$3,Sheet1!$A:$A,0))

rngValue

=INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$3,Sheet1!$A:$A,0))

You can then click the series in the chart and modify the formula to:

=SERIES(Sheet1!$B$1,Sheet1!rngDay,Sheet1!rngValue,1)

enter image description here

Here's a nice Chandoo post on how to use dynamic ranges in charts.

Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • Could you use OFFSET(rngDay,0,1) as well for the reference to save a bit of typing? – AER Nov 24 '14 at 00:49
  • I think so. See if it works for you. I'd probably leave it independent and avoid the use of OFFSET, but neither of those are critical. – Doug Glancy Nov 24 '14 at 02:09
4

Just another answer for bits and googles..

If you still want to refer to your start and end cells, you'll need to add a separate formula for your Day Range and your Values Range. Formulas are below and the screenshot shows the formulas used.

Day Range:

="Sheet1!"&$F$2&":"&ADDRESS(ROW(INDIRECT($F$3)),COLUMN(INDIRECT($F$2)))

Values Range:

="Sheet1!"&ADDRESS(ROW(INDIRECT($F$2)),COLUMN(INDIRECT($F$3)))&":"&$F$3

enter image description here

Then add two ranges referencing the INDIRECT values of those cells

Press Ctrl+F3, Click New, and add a new range with the name "chart_days", referring to =INDIRECT(Sheet1!$F$4); and a new range with the name "chart_values", referring to =INDIRECT(Sheet1!$F$5)

Finally, in your chart, add a series that refers to =nameOfYourWorkbook!chart_values

and Edit the category to refer to =nameOfYourWorkbook!chart_days

Jaycal
  • 2,087
  • 1
  • 13
  • 21
  • 2
    Thanks `Jaycal`, it's just a shame you didn't put the `F4/F5` formulas into your answer itself, we can't copy/paste from screenshots :( – Max Aug 19 '13 at 08:04
0

I use OFFSET to create a defined name formula so that I may define all the ranges for the data, allowing me to have a starting a ending date (or the beginning and ending position of any data set).

For a simple graph, I define the name CategoryLabels as follows:

= OFFSET($A$5;  (InicitialMonth-1);  0;  LastMonth - (InitialMonth-1))

and DataCars as follows:

= OFFSET($B$5;  (InicitialMonth-1);  0;  LastMonth - (InitialMonth-1))

You will have to define as many names as Series you want to include, following the same procedure. In this simple case, I only included Car sales.

Initial Month and Last Month are Range Names defined for single cells used to indicate which months of the graph will be included (from starting to ending months).

Remember, as explained by Sean Cheshire and others, that to use the names for the chart values, the name of the spreadsheet must be included.

adamdunson
  • 2,635
  • 1
  • 23
  • 27
Luis G. Rodriguez
  • 1
0

According to the formula you have shown: =INDIRECT("sheet!"&E2&":"&E3) you are not naming the sheet correctly.

I would have thought it would be Sheet1! or Sheet2! etc. Your formula resolves to =sheet!E2:E3 which is not a valid address. The error message you are getting means Excel cannot resolve the input to INDIRECT. INDIRECT is a valid function so the argument you offer it must be invalid.

All of the above answers which state the sheet name have corrected your error but do not mention it... ;)

KiwiSteve
  • 116
  • 1
  • 6
  • 1
    This would be better summarized in a comment. Even if the typo is fixed, Excel will still not let you put an INDIRECT in a chart data range. – Nattgew Mar 10 '16 at 20:42
0

Named formula with Indirect functions DOES NOT WORK IN CHARTS. It works in other froms as your desired dynamic source will be highlighted, but when you it in chart, it would not be evaluated. Hope Microsoft put a fix on this.

Phil Cheesesteak
  • 1
0

When a line chart's range is a named variable, and the variable has INDIRECT() references through a cell to a range, then the variable must have at least 2 INDIRECT()s separated by a comma.

singduck
  • 1