Reference(s) to worksheets for building graphs

[PvZ]

In my workbook I have multiple sheets.
I have a summary sheet, called: Graphs, consisting of (only) graphs,
build from the data from the various sheets. These graphs do not change.
I would like to determine (via the Graph-sheet), from which worksheet
the data should come from.

For a (particular) graph, the [chart data range] is, e.g.:
='Sheet100'!$F$6:$F$10
The value: "Sheet100" is what 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

 

[OssieMac]

Hi Paul,

Am I correct in my interpretation of your post that the data on each of the
worksheets is the same type of data with the same column headers but with
different values and you want to have just one graph but change the data for
the graph by selecting a different worksheet?

If my interpretation is correct, have you thought about having all the data
on one worksheet and using AutoFilter. You can set up your graph on the
AutoFiltered range and the graph becomes dynamic by displaying the series for
the visible cells on the worksheet after the filters are set. You simply need
a column in the AutoFiltered range that reflects each group of data which
currently is reflected by which sheet the data in on.

If the above is an option and you can't work out how to achieve the end
result then let me know what type of charts (graphs) and I will attempt to
provide some tuition to get you started.

Also what version of Excel are you using?

 

[PvZ]

Hi OssieMac,

thanks for taking the time to reply.
Yes, your interpretation is correct.
I am using: Microsoft Office Excel 2007 (12.0.6529.5000)

I also fully understand your solution and yes, I think that could work.
Although it is not the (preferred) solution I was looking for.
(the assignment here was that the owners of the various data (=worksheets)
would be able to all make the same graphs (to be used in their ppt files),
by simply selecting their worksheet. Depending on the need, I could simply
define new graphs, that would work for all sheets/data-owners.
Autofilter will work, but would not give me the desired solution/WoW.)

Anyhow, if nothing betters turns up, I will use your recommendation.
Will let you know how it turned out . . .
Thanks again !
Kind Regards,

Paul

 

[Roger Govier]

Hi Paul

I guess you missed the solution I posted to your first question
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

Hi OssieMac,

thanks for taking the time to reply.
Yes, your interpretation is correct.
I am using: Microsoft Office Excel 2007 (12.0.6529.5000)

I also fully understand your solution and yes, I think that could work.
Although it is not the (preferred) solution I was looking for.
(the assignment here was that the owners of the various data (=worksheets)
would be able to all make the same graphs (to be used in their ppt files),
by simply selecting their worksheet. Depending on the need, I could simply
define new graphs, that would work for all sheets/data-owners.
Autofilter will work, but would not give me the desired solution/WoW.)

Anyhow, if nothing betters turns up, I will use your recommendation.
Will let you know how it turned out . . .
Thanks again !
Kind Regards,

Paul

In my workbook I have multiple sheets.
I have a summary sheet, called: Graphs, consisting of (only) graphs,
build from the data from the various sheets. These graphs do not change.
I would like to determine (via the Graph-sheet), from which worksheet
the data should come from.

For a (particular) graph, the [chart data range] is, e.g.:
='Sheet100'!$F$6:$F$10
The value: "Sheet100" is what 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

 

[PvZ]

Dear Roger,

Sure missed your solution (until now).
To be honest: I have difficulty in applying your solution, as I do
not understand all.
1) Listing the worksheet names in $A$1:$A$20 is O.K.
2) Using $B$1 to list the preferred worksheet name for the graph(s) is O.K.
however:
3) creating a named range and how to apply this in the formatting of
the graph; I do not understand.
Could you please explain a bit more on this, please ?
Thanks in advance, Kind regards,

Paul

Hi Paul

I guess you missed the solution I posted to your first question
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

Hi OssieMac,

thanks for taking the time to reply.
Yes, your interpretation is correct.
I am using: Microsoft Office Excel 2007 (12.0.6529.5000)

I also fully understand your solution and yes, I think that could work.
Although it is not the (preferred) solution I was looking for.
(the assignment here was that the owners of the various data (=worksheets)
would be able to all make the same graphs (to be used in their ppt files),
by simply selecting their worksheet. Depending on the need, I could simply
define new graphs, that would work for all sheets/data-owners.
Autofilter will work, but would not give me the desired solution/WoW.)

Anyhow, if nothing betters turns up, I will use your recommendation.
Will let you know how it turned out . . .
Thanks again !
Kind Regards,

Paul

In my workbook I have multiple sheets.
I have a summary sheet, called: Graphs, consisting of (only) graphs,
build from the data from the various sheets. These graphs do not change.
I would like to determine (via the Graph-sheet), from which worksheet
the data should come from.

For a (particular) graph, the [chart data range] is, e.g.:
='Sheet100'!$F$6:$F$10
The value: "Sheet100" is what 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

.