D
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
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