Is it possible to use INDIRECT into a named range formula as a chart data source?

[fabiospark]

Let me explain.

I have a few identically structured sheets with different names: LM1 LM2 LM3 etc.
In each of them there is an identically structured chart where the sources for its 10 series are defined
by 10 dynamic sheet level named ranges with OFFSET functions who refers to both sheet LM1 and LM1sto.
By now I created the named range for the first series of the first sheet in this shape [=OFFSET(LM1sto!$B$8;0;;COUNTIF(LM1sto!$FG:$FG;">0")
where only the range into the countif ($FG:$FG) will be different for each series.
Then, with VBA I created the names for each of the other sheets changing just the sheet name.

Then I started to think if it would be possible to define these names using INDIRECT to get all the sheet related info from some cells into the sheet itself
so I would be able to use only 10 worbook level names instead of 100 sheet level names. This is what I mean:
LM1!Ch_TRPF = OFFSET(INDIRECT($I$1&"sto!$B$8");$J$29;;$K$29-$J$29+1)
where:
LM1!Ch_TRPF = Name of the range that holds the values
$I$1 = LM1 (sheet name)
$JK$29 = index value for the starting date of the data set to show
$K$29 = index value for the ending date of the data set
$K$29-J$29+1 = numbers of row of the dataset between starting and ending dates

Do you think I could use Ch_TRPF as a workbook level name for all the sheet and it will
pick up the values from the sheet the chart is on to?
If not directly, is there an inderect way (VBA)?

Hope I made myself clear.

Thanks.

 

[GlennUK]

Have you considered having a single charting sheet that fetches the data from whatever sheet is chosen, for a single chart ... so you view any data you want from a dropdown, for example?

 

[fabiospark]

Thanks for your answer.
I have a different sheet for different portfolios and I need to see the chart beside the numerical data
and also, for each portfolio I have a sheet called with the same pf name plus "sto" at the end, where
I daily save all the significant data of the pf. Should I have a single sheet for all the pfs, with a dropdown
to select which pf I'd like to see, would complicate the way I record the pf values into the archive sheet.
Thanks.

 

[GlennUK]

No, you are not understanding me at all. Keep a different sheet for each portfolio, just have 1 chart sheet, with formulas to fetch the numerical data from the chosen sheet, and feed that into the chart.

 

[fabiospark]

Yes I understood, I think, but I need each pf chart to be shown besides its data and its data must be on different sheets
so I need one chart for each pf sheet. That's what I mean. Thanks.

 

[GlennUK]

I'm asking why you need a chart on each sheet? If you have a chart sheet that allows you to select which sheets data you want to chart (and show the data too, as you want), then how is that not giving you exactly what you need?

 

[fabiospark]

Because to save all the data of one pf together - and only of that pf, on another sheet, I would have to select which pf I want to save and then save it,
than select another pf and do it again, and so on, and for me (I'm an amater, with Excel and VBA) it's easier having just to copy one row for each pf into
its "xxxsto" sheet.
Anyway, don't get me wrong, I appreciate the time and effort you are spending to try and help me but I think they would be better spent in trying
to actually answer what an OP asks, once she or he has confirm you he needs what he asked for.
Thanks.
fabio