Chart Source Data
 

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.

Chart Source Data
 

Goto Insert - Name - Define.

Define a named range with a formula similar to:
=INDIRECT("B2:D4")
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!MyNamedRa nge)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"DanielC" wrote:

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.

Chart Source Data
 

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Luke M" wrote in message
...
Goto Insert - Name - Define.

Define a named range with a formula similar to:
=INDIRECT("B2:D4")
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!MyNamedRa nge)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"DanielC" wrote:

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.

Chart Source Data
 

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Andy Pope" wrote:

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Luke M" wrote in message
...
Goto Insert - Name - Define.

Define a named range with a formula similar to:
=INDIRECT("B2:D4")
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!MyNamedRa nge)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"DanielC" wrote:

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.

.

Chart Source Data
 

Further investigation...

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

=INDIRECT("B2:D4")

should be:
=INDIRECT("Sheet1!B2:D4")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Andy Pope" wrote:

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Luke M" wrote in message
...
Goto Insert - Name - Define.

Define a named range with a formula similar to:
=INDIRECT("B2:D4")
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!MyNamedRa nge)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"DanielC" wrote:

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.

.

Chart Source Data
 

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

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Luke M" wrote in message
...
Further investigation...

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

=INDIRECT("B2:D4")

should be:
=INDIRECT("Sheet1!B2:D4")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Andy Pope" wrote:

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Luke M" wrote in message
...
Goto Insert - Name - Define.

Define a named range with a formula similar to:
=INDIRECT("B2:D4")
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!MyNamedRa nge)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"DanielC" wrote:

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.

.