Using a Cell Value to Adjust a Data Series for a Plot

[Don]

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

 

[Ed Ferrero]

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(ADDRESS($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/tabid/102/Default.aspx
look for the Reporting sample.

Ed Ferrero
www.edferrero.com

 

[Don]

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