|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 24th, 2009, 04:51 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
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. 
|
|
#2
November 24th, 2009, 08:53 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
Chart Source Data
Goto Insert - Name - Define.
Define a named range with a formula similar to: =INDIRECT("B2  4")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.
|
|
#3
November 25th, 2009, 11:32 AM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 4")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.
|
|
#4
November 25th, 2009, 01:48 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 4")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. .
|
|
#5
November 25th, 2009, 02:03 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 4")should be: =INDIRECT("Sheet1!B2 4")-- 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 4")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. .
|
|
#6
November 25th, 2009, 02:30 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 4")should be: =INDIRECT("Sheet1!B2 4")-- 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 4")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. .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
