Prev: How does a function provide a blank cell as a result
Next: Reference(s) to worksheets for building graphs
From: PvZ on 31 May 2010 07:06
Max,

can you (also) help me out on this one:
I also have a summary sheet, called: Graphs, but consisting of graphs.
For a (particular) graph, the [chart data range] is: ='Sheet100'!$F$6:$F$10
The value: "Sheet100" I would like to choose/vary;
- preferably by selecting a cell with the name of the sheet I want the
graph(s)
made from (e.g. A1=Sheet100; A2=Sheet200 aso.
By seecting Cell A2 (on sheet: Graphs) the graphs will be build using the
data on Sheet200 . . . , or
- by typing the name of a worksheet in a (fixed) cell
(e.g. typing: Sheet200 in Cell A1)
Note: all the worksheets have the same columns (but may vary in number of
rows, although this could be also made the same).
Hope you understand my question !

Regards,

Paul

"Max" wrote:

> In your summary sheet,
>
> Assuming the sheetnames are listed in A2 down, eg: Sheet2, Sheet3, etc,
> with the ranges to be summed listed in B1 across, eg: A2:A10, B2:B100, etc
> you could place this in B2:
> =SUM(INDIRECT("'"&$A2&"'!"&B$1))
> then simply copy across and fill down to populate the table
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "M.Moncrief" wrote:
> > How can I use the INDIRECT formula to return the sum of specific cells in a
> > data worksheet. My summary worksheet has a cell that requires the sum of the
> > same 4 cells in each corresponding data worksheet. Any tips on modifying to
> > make it work?
From: Roger Govier on 31 May 2010 07:26
Hi

One way
With a list of your sheet names in A1:A20 of sheet Graphs
Create a named range called GraphRange with a value of
=INDIRECT(INDEX(Graphs!$A$1:$A$20,Graphs!$B$1)&"!$F$6:$F$10")

Use GraphRange as the source for your graph data.

Enter the row number of the sheet you want in cell B1, and the source
data for the graph will change accordingly
--
Regards
Roger Govier

PvZ wrote:
> Max,
>
> can you (also) help me out on this one:
> I also have a summary sheet, called: Graphs, but consisting of graphs.
> For a (particular) graph, the [chart data range] is: ='Sheet100'!$F$6:$F$10
> The value: "Sheet100" I would like to choose/vary;
> - preferably by selecting a cell with the name of the sheet I want the
> graph(s)
> made from (e.g. A1=Sheet100; A2=Sheet200 aso.
> By seecting Cell A2 (on sheet: Graphs) the graphs will be build using the
> data on Sheet200 . . . , or
> - by typing the name of a worksheet in a (fixed) cell
> (e.g. typing: Sheet200 in Cell A1)
> Note: all the worksheets have the same columns (but may vary in number of
> rows, although this could be also made the same).
> Hope you understand my question !
>
> Regards,
>
> Paul
>
> "Max" wrote:
>
>> In your summary sheet,
>>
>> Assuming the sheetnames are listed in A2 down, eg: Sheet2, Sheet3, etc,
>> with the ranges to be summed listed in B1 across, eg: A2:A10, B2:B100, etc
>> you could place this in B2:
>> =SUM(INDIRECT("'"&$A2&"'!"&B$1))
>> then simply copy across and fill down to populate the table
>> --
>> Max
>> Singapore
>> http://savefile.com/projects/236895
>> xdemechanik
>> ---
>> "M.Moncrief" wrote:
>>> How can I use the INDIRECT formula to return the sum of specific cells in a
>>> data worksheet. My summary worksheet has a cell that requires the sum of the
>>> same 4 cells in each corresponding data worksheet. Any tips on modifying to
>>> make it work?
 | 
Pages: 1
Prev: How does a function provide a blank cell as a result
Next: Reference(s) to worksheets for building graphs