reference to ThisSheet in in Excel chart

**twd000** · 04-20-2010, 06:24 PM

I have a tab/worksheet with several named ranges

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

=OFFSET('Curve Fitter (TC#116)'!$C$13,0,0,COUNTA('Curve Fitter (TC#116)'!$D:$D)-1)

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

Series1>XValues = ='Curve Fitter (TC#116)'!plot_time

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** · 04-21-2010, 02:41 AM

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** · 04-21-2010, 03:31 AM

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** · 04-21-2010, 06:01 AM

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** · 04-21-2010, 02:01 PM

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** · 04-21-2010, 03:13 PM

Originally Posted by 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.

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