Multi-sheet data charting problem

Hi folks,

I'm relatively new to the "joys" of Excel, so go easy!

I have a multi-sheet workbook. Each page has a cell containing a total
- let's call it "D2".

I want to take data from cell "D2" on each sheet and make a "clustered
column" chart.

I also want each column to take the name of each sheet, thus:

__
__ __
__ __ __
__ __ __
|______|______|______|
sheet1 sheet2 sheet3 etc.

Is this possible? I have tried using the Chart Wizard but, when I
select cell D2 across all sheets, thus:

Data range: ='sheet1:sheet4'!$D$2

....it returns an error: "Reference is not valid". Presumably this is
because I'm trying to create a chart using only one cell from each
sheet... any advice?

Is there potentially a better way to manage my data than the way I'm
doing it now (without re-doing the whole thing)?

ARGH!

TIA
Jim

Jon Peltier has instructions for charting data from different sheets:


http://www.peltiertech.com/Excel/Cha...entSheets.html

Jim M wrote:
> Hi folks,
>
> I'm relatively new to the "joys" of Excel, so go easy!
>
> I have a multi-sheet workbook. Each page has a cell containing a total
> - let's call it "D2".
>
> I want to take data from cell "D2" on each sheet and make a "clustered
> column" chart.
>
> I also want each column to take the name of each sheet, thus:
>
> __
> __ __
> __ __ __
> __ __ __
> |______|______|______|
> sheet1 sheet2 sheet3 etc.
>
> Is this possible? I have tried using the Chart Wizard but, when I
> select cell D2 across all sheets, thus:
>
> Data range: ='sheet1:sheet4'!$D$2
>
> ...it returns an error: "Reference is not valid". Presumably this is
> because I'm trying to create a chart using only one cell from each
> sheet... any advice?
>
> Is there potentially a better way to manage my data than the way I'm
> doing it now (without re-doing the whole thing)?
>
> ARGH!
>
> TIA
> Jim


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

In article <[email protected]>, Debra Dalgleish
<[email protected]> wrote:

> Jon Peltier has instructions for charting data from different sheets:
>
> http://www.peltiertech.com/Excel/Cha...entSheets.html

Sorry, but I've already looked at that page and none of it means much
to me. As I've mentioned, I know hardly anything about Excel.

/And yet I'm forced to use it against my will!!/

Just kidding... ;o)

Jim