Question regarding "INDIRECT" and Charts

[David F. Schrader]

I have a spreadsheet with a single worksheet which is updated
monthly with data. The spreadsheet also contains multiple charts
based upon the data of that "raw data" sheet.

One "set" of charts represents a block of 6 months of comparative
data. Another a set of 12 month data. Unfortunately as it now
stands I am required to edit each of the charts to alter the "starting"
and "ending" points for the plotting of the data points. I do this by
right-clicking on the chart and choosing "Source Data" and then the
"Series" tab. Then, on that tab I can modify each of the settings for
"Values" and "Category (X) axis labels."

Examples of these would be (last month & this month) :

='Raw Data'!$V$3:$AI$3 -----> ='Raw Data'!$W$3:$AJ$3
='Raw Data''!$V$7:$AI$7 -----> ='Raw Data'!$W$3:$AJ$3
='Raw Data'!$V$9:$AI$9 -----> ='Raw Data'!$W$3:$AJ$3

which is repeated throughout a whole set of charts. My work load
would be significantly eased *IF* I could use the Excel INDIRECT
function which would seem to be perfect for the task. Unfortunately
every way I've tried to insert it in has resulted in Excel spitting back
an error message.

I've tried something like:
='Raw Data'!$INDIRECT('Raw Data'!$A$1)&3: ( assume a
continues line) $INDIRECT('Raw Data'!$A$2)&3
where 'Raw Data'!$A$1 = "V" and 'Raw Data'!$A$2 = "AI" but
Excel (2003) refuses to allow that.

Ideas and/or suggestions on how I might use INDIRECT to make
my work easier?

Many thanks in advance and a carton of virtual Oreo cookies too.

David

 

[Tom Ogilvy]

Assume I will put my range address in cell B9 without the sheet name.

put in W3:AJ3
in Cell B9 of 'Raw Data' as an example

So I want to create a defined name to utilize that information


Insert=> Name=> Create

Name: Source1
Refersto: "=Indirect("'Raw Data'!" & 'Raw Data'!$B$9")


in the chart, instead of
='Raw Data'!$W$3:$AJ$3

put in
='My WorkbookName'!Source1

Create similar defined names for other ranges.