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
|
|||
|
|||
Using a Cell Value to Adjust a Data Series for a Plot
Is it possible to redirect the data source series of a plot to a cell
location so that it can be manipulated? For example, the current data series is ='Spending Data'!$A$32:$A$43. I would like to be able to adjust the series by entering a value in a cell. For example, using the existing range as a baseline, entering a "1" in the magic cell would change the series to ='Spending Data'!$A$33:$A$44. I have tried using INDIRECT, but either I don't understand how to correctly use it, or its use in a graph data series assignment, or both. A pointer to an online example would be great. Thanks! Don |
#2
|
|||
|
|||
Using a Cell Value to Adjust a Data Series for a Plot
Hi Don,
You need to combine INDIRECT and ADDRESS like this; =INDIRECT(ADDRESS(row, column,1, TRUE, sheet)) where; row = cell containing row no column = cell containing column no sheet = cell containinig sheet name of the data you are trying to get. And you can get fancy, like =SUM(INDIRECT(ADDRESS($A$1,$B$1,,,)):INDIRECT(ADDR ESS($A$2,$B$2,,,))) to sum a range defined by row/column no entries in four cells. There is a sample here http://www.edferrero.com/ExcelCharts...2/Default.aspx look for the Reporting sample. Ed Ferrero www.edferrero.com |
#3
|
|||
|
|||
Using a Cell Value to Adjust a Data Series for a Plot
Ed, Well, I tried both =ADDRESS($A$30, $A$31,1, TRUE,"Spending Data"):ADDRESS(($A $30+10), $A$31,1, TRUE,"Spending Data") and =INDIRECT(ADDRESS($A$30, $A$31,1, TRUE,"Spending Data")):INDIRECT (ADDRESS(($A$30+10), $A$31,1, TRUE,"Spending Data")) in the 'Series Values:' text box of the 'Edit Series' window and keep getting a "That Function is not Valid" error. Based on tinkering with inputs to the 'Series Values:', I am beginning to think that maybe it cannot contain functions like INDIRECT and ADDRESS. Although I have not been able to find a reference stating such. The approach you used in the Reporting sample makes a lot of sense and in a way validates that 'Series Values:' should not be mucked with. Thanks for the suggestions! Don On Sep 30, 9:02*pm, "Ed Ferrero" wrote: Hi Don, You need to combine INDIRECT and ADDRESS like this; =INDIRECT(ADDRESS(row, column,1, TRUE, sheet)) where; row = cell containing row no column = cell containing column no sheet = cell containinig sheet name of the data you are trying to get. And you can get fancy, like =SUM(INDIRECT(ADDRESS($A$1,$B$1,,,)):INDIRECT(ADDR ESS($A$2,$B$2,,,))) to sum a range defined by row/column no entries in four cells. There is a sample herehttp://www.edferrero.com/ExcelCharts/tabid/102/Default.aspx look for the Reporting sample. Ed Ferrerowww.edferrero.com |
Thread Tools | |
Display Modes | |
|
|