Chart Source Data

[DanielC]

Hi all. I am making some charts and need some help with the cells the chart
references as source data.

I am trying to make it so that I can insert and delete rows while having the
chart reference the original cell, rather then automatically updating and
following the cell when I add or remove rows. Basically trying to do the same
thing in the chart as I can do with a formula by using the "indirect" feature.

Is this possible with a chart? Having to go back in and manually change the
references back to the original cells is time consuming each week.

 

[Luke M]

Goto Insert - Name - Define.

Define a named range with a formula similar to:
=INDIRECT("B24")
With the range in quotes referring to your source data.

Select your existing chart, and select the series. You should be able to see
a formula in the formula bar controlling the series, structure similar to:
=SERIES(Sheet1!Name,Sheet1!Labels,Sheet1!Values)

Replace the appropriate portion of formula with named range like so:
=SERIES(Sheet1!Name,Sheet1!Labels,Sheet1!MyNamedRange)

 

[Andy Pope]

Hi Luke,

Did you try this?
Charts and INDIRECT do not normal play together nicely. I get the following
error message.
"A formula in this worksheet contains one or more invalid references.
Verify that your formulas contain a valid path, workbook, range name, and
cell reference."

Same named formula is happy to work within spreadsheet cells.

Cheers
Andy

 

[Luke M]

Andy,

Yes, I did try it. While you can't use the INDIRECT function directly within
the SERIES "formula" by using it in a named range, and then having the chart
callout the named range, it worked just fine for me. To clarify, I'm using
the same referencing techniques you would use to create a dynamic chart (in
this case, the result is the exact opposite, but same idea).

Let me know if you still have questions.

 

[Luke M]

Further investigation...

I believe the error that may be popping up is caused not by the use of
INDIRECT, but through the reference.

=INDIRECT("B24")

should be:
=INDIRECT("Sheet1!B24")

 

[Andy Pope]

Thanks for the clarification.
The addition of the sheetname does indeed allow the indirect to work.

Cheers
Andy