42 sheets of data and i only want 1 graph !

[WayneF]

Hi all,

I have 42 sheets numbered 1 through to 42. (They were/are imported)
I have created 1 graph on a seperate sheet. At the moment this graph
plots the data from sheet 1

The layout of data on all sheets are the same ...only the values change.
cell ranges are the same

Is there a way to automatically tell the graph to plot from data on
another sheet ?

Maybe just a small input from the user ? Like to type 16 in a cell and
then the graph will use the data from the cell to plot the data from "sheet"
16 ?

Thanks for any help

Best regards,

Wayne

 

[Ken Wright]

Take a look at the INDIRECT function. This allows you to build a reference
from strings of data, meaning you could have a single cell on your graph
sheet to contain the sheet name you wish to pull, and then build a table of
values from references using INDIRECT. Then simply changing the sheet name
in that one cell will bring in the relevant data.

Eg, assuming you had cell A1 containing the Sheet name of whatever sheet you
want to pull, and the values from each sheet were in cells B2:B6, then you
could set up on your chart sheet a set of values like this

=INDIRECT(A1&"!B2")
=INDIRECT(A1&"!B3")
=INDIRECT(A1&"!B4")
=INDIRECT(A1&"!B5")
=INDIRECT(A1&"!B6")

If 1 were your sheet name, then it would pull data from sheet 1, or change
it to 5 and it will pull from sheet 5.

Note the syntax changes slightly if your sheet names have any spaces in
them:-

=INDIRECT("'"&A1&"'!B2")

 

[Don Guillett]

=INDIRECT("'"&A1&"'!C1:C21")

thats " ' " & a1 & " '!c1"
if using the sheet name in cell a1

 

[Don Guillett]

Forgot to mention that I would do this as a defined name such as WaynesWorld
and in the refers to box
=the indirect formula
then in the chart use
=putyourfilenamehere.xls!WaynesWorld
as the source

 

[WayneF]

Thanks for that ...

Just one other question, I have 1000 rows to use !

With your formula I cant drag down and have the B2, B3, B4 etc continue
?

Doe this mean i have to enter 1000 lines ?

Thanks in advance !

Wayne

 

[Bernie Deitrick]

Wayne,

Enter this into row 2 to get B2, then drag down for 1000 rows.

=INDIRECT($A$1&"!" & ADDRESS(ROW(),2))

HTH,
Bernie
MS Excel MVP

 

[WayneF]

Thanks Bernie

I am just happy that i can help out somebody else in my field of
bussiness as you guys do here !

Thanks to all for there post and keep up the good work ! Respect

Wayne