Change sheet names for charts on the fly

[Arlen]

Hello, Everyone!

The following is a static formula that charts data from the Hypo! sheet.

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

I need to replace Hypo with the name of the sheet the user selects. I tried:

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

but Microsoft says "That function is not valid." Is this a syntax issue, or
can you not use INDIRECT with SERIES?

Any help would be appreciated.

Have a great day.

Arlen

 

[M Kan]

Try adding a worksheet named chart data, reference the Chart to the data
table there, and then use INDIRECT to modify the referenced chart data on the
fly

 

[Suleman Peerzade]

This might help you if i have got your problem right.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)