ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Charts and Charting in Excel (https://www.excelbanter.com/charts-charting-excel/)
-   -   Using INDIRECT in the SERIES function...possible? (https://www.excelbanter.com/charts-charting-excel/196840-using-indirect-series-function-possible.html)

Arlen

Using INDIRECT in the SERIES function...possible?
 
Here is a static formula for the Series "Loads"

=SERIES(Hypo!$I$2,Hypo!Labels2,Collect.xls!Loads2, 4)

This only graphs what is on the Hypo page. (By the way, why is the last
Hypo subbed with Collect.xls?)

I need instead to switch between 3 pages. I tried the following:

=SERIES(INDIRECT("'"& $O$2 &"'!$I$2"),INDIRECT("'"& $O$2
&"'!Labels2"),INDIRECT("'"& $O$2 &"'Loads2"),4)

and Excel simply says That is not a valid function. The end.

Why doesn't this work? Each individual piece returns the right information.

I appreciate any help you got. Have a great day!

Arlen


Jon Peltier

Using INDIRECT in the SERIES function...possible?
 
The references in a series formula have to reference ranges, not
calculations. Define names like this:

name
=INDIRECT("'"&$O$2&"'!$I$2")

xvals
=INDIRECT("'"& $O$2 &"'!Labels2")

yvals
=INDIRECT("'"& $O$2 &"'Loads2")

and make your series formula look like this:

=SERIES(Hypo!name,Hypo!xvals,Hypo!yvals,4)

The reason one of the Hypo references changes to Collect.xls is that the
reference that changed is to a name that is defined for the worksheet (its
name is Loads2), while the one that didn't change is defined for the sheet
Hypo (its name is Hypo!Labels2).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Arlen" wrote in message
...
Here is a static formula for the Series "Loads"

=SERIES(Hypo!$I$2,Hypo!Labels2,Collect.xls!Loads2, 4)

This only graphs what is on the Hypo page. (By the way, why is the last
Hypo subbed with Collect.xls?)

I need instead to switch between 3 pages. I tried the following:

=SERIES(INDIRECT("'"& $O$2 &"'!$I$2"),INDIRECT("'"& $O$2
&"'!Labels2"),INDIRECT("'"& $O$2 &"'Loads2"),4)

and Excel simply says That is not a valid function. The end.

Why doesn't this work? Each individual piece returns the right
information.

I appreciate any help you got. Have a great day!

Arlen





All times are GMT +1. The time now is 08:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com