reference to ThisSheet in in Excel chart

[twd000]

I have a tab/worksheet with several named ranges

The named ranges are defined in this format:
Insert>Name>Define "plot_time"

Please Login or Register  to view this content.

Then these names are used to plot the data in a Line Chart:

Please Login or Register  to view this content.

This works fine except I need to copy another version of the entire worksheet. Now the new sheet has a new name of course. So my hard-coded links are broken. I need something like the equivalent VBA ThisWorkbook, which would be dynamically updated to reference ThisSheet containing the current chart.

[DonkeyOte]

If I've understood you could perhaps try defining the names as:

Name: plot_time
RefersTo: =OFFSET(!$C$13,0,0,COUNTA(!$D:$D)-1)

and use use !plot_time as your reference ?

(I suspect the Chart ref. might not work - you may need a Chart whizz to help on that front I'm afraid... I am not one I'm afraid)

perhaps check out Jon Peltier's post: http://www.eggheadcafe.com/software/...--problem.aspx

[DonkeyOte]

FWIW I'm convinced the above doesn't help you so apologies ... in essence it just means you have one name in the file which is "relative" to the sheet however I can find no way (as yet) of using this name in the Chart Series.

Hoping Andy Pope and the like might step in and save both you & I...

[Andy Pope]

It's not clear to me whether you want a VBA solution or not.

Using a workbook level named ranged means the chart will still refer to the original worksheets cells. So you would need to create a completely new named range and update the chart.

If you use a worksheet level named range you will find this converted to a static array in the newly copied chart, so this approach will not work.

[twd000]

Donkey, good idea but there is no way to define a named range without the worksheet name reference. Even if you just select cells A1:Z1, Excel fills in the sheet name automatically, so it is hard-coding the reference to the current sheet.

Andy, I would prefer a non-VBA solution, just using worksheet functions. I want this solution to work when the sheet is copied multiple times.

Can you explain the scope of named ranges? The difference between workbook- and worksheet-level ranges i not clear to me. Is there one kind that will automatically update the named range to reference the current shet?

[DonkeyOte]

Donkey, good idea but there is no way to define a named range without the worksheet name reference. Even if you just select cells A1:Z1, Excel fills in the sheet name automatically, so it is hard-coding the reference to the current sheet.

Not that it resolves your charting issue but regards the above, did you test the suggestion ?

[twd000]

Not that it resolves your charting issue but regards the above, did you test the suggestion ?

you're right, the GUI will let you define the named range without the sheet name (does this make it a workbook-level range?), but the chart will not let you use that reference without specifying sheet name

A workbook-level named range might interfere when I copy the sheet and there are multiple instances of the same workbook-level name

[shg]

There can only be one instance of a given workbook-level name.

[DonkeyOte]

The name suggested will work relative to the sheet "natively" ie - in the context of worksheet functions - for ex. (though of no use)

=ROWS(plot_time)

would work on each sheet and get appropriate results, however, you can not utilise this name when it comes to Chart Series and it was upon realisation of that fact that I posted to the effect that it was of no use.

shg might have an answer for you on the series issue - I don't I'm afraid.

[twd000]

I found this nugget of wisdom on another page:

does this mean I am trying to do something which Excel has declared impossible?

Step 4 - Series Formula Rules and Facts


Range references in a SERIES formula are always absolute and always contain a worksheet name. If you have defined named ranges then the series formula can contain a named range in place of a range.


Any range references used are always prefixed with the worksheet name.


Any range references used are always absolute references (as opposed to relative).


If you accidently remove the worksheet name or use a relative reference, these will be changed automatically.


Each data series has a corresponding series formula and the number of arguments will depend on the chart type.