Include sheetname dynamically in named range?
 

I have a workbook with about 35 sheets in it and a chart that
repeats on each page with the current data. I would gladly convert
it to a dynamically ranged chart (so I can just replicate it on
each sheet without editing the data source), but the problem is
getting the worksheet name into the range. Can someone point out
how I might do that?

Thanks for any help.

Include sheetname dynamically in named range?
 

hi, Dallman Ross !

I have a workbook with about 35 sheets in it and a chart that repeats on each page with the current data.
I would gladly convert it to a dynamically ranged chart
(so I can just replicate it on each sheet without editing the data source)
but the problem is getting the worksheet name into the range.
Can someone point out how I might do that?

FWIW & AFAIK, embedded charts remains linked to its original data source so,
cut/copy & paste embedded charts from one sheet to another, does not change the original links

one way could it be to have a sheet (chart embedded w/ formats) as "template",
copy template-sheet within same workbook and modify (current chart source) data in the new sheet
even with named ranges, excel can handle a new names set (global & local) for each sheet

or... vba ?

hth,
hector.

Include sheetname dynamically in named range?
 

In , "Héctor Miguel"
spake thusly:

I have a workbook with about 35 sheets in it and a chart
that repeats on each page with the current data. I would
gladly convert it to a dynamically ranged chart (so I can just
replicate it on each sheet without editing the data source) but
the problem is getting the worksheet name into the range. Can
someone point out how I might do that?

one way could it be to have a sheet (chart embedded w/ formats)
as "template", copy template-sheet within same workbook and
modify (current chart source) data in the new sheet even with
named ranges, excel can handle a new names set (global & local)
for each sheet

or... vba ?

Thanks, Hector. I guess it's not possible or easy without
external help, then. I suppose I could put the name of the page
in a cell somewhere and reference that in the named range, though,
via INDIRECT. But in any case I'd be hard-coding data. The
template is not a bad idea. Even VBA is doable. It's not
that hard for me to change the source data, but it's just
a mild annoyance.

Thank you again.

=dman=