using relative references in range names

I have a workbook with a single worksheet. The worksheet includes some named ranges, which are used in a dynamic chart (so that when I add data, the chart updates).

I want to duplicate worksheet#1 so that I can have worksheets 2, 3, 4 etc.

Thus I can paste new data in the new worksheets, and have all my calculations done.

Naturally, the only probelm is the graph. It is using the named ranges from worksheet #1.

Is there a solution, so that I don't have to re-create my graphs on each worksheet? (each worksheet has varying numbers of rows, AND, I will be updating each worksheet with new data from time to time, thus the need for a dynamic chart)

Thanks

Change your range names to have sheet scope. Take, for example, rgnX, your x values, and assume the sheet is named Sheet1

1. Do Insert > Name > Define, and select rgnX.

2. In the name box, prepend Sheet1!, so it reads Sheet1!rgnX

3. Hit OK.

Now there are two named ranges rgnX. When a reference appears in Sheet1, it will refer to the range with Sheet1 scope; when a reference appears on any other sheet, it will be referring to the range having workbook scope (though they're both referring to the same range in the example, that need not be true).

4. Go to a different sheet, and do Insert > Name > Define, and delete rgnX.

In practice, you'd repeat steps 1-3 for all the names on the sheet, and then do step 4 for all the names.

Now when you copy Sheet1, it will have it's own set of names with sheet scope.

Capische?

And in the future, when you create named ranges, they should always have sheet scope unless there's a particular reason not to.

shg

thanks I'll give it a try.

Update: I'm using Excel 2007.

It has no "insert > name > define" function but there is a "Name Manager." Rather than going through steps one through three, I simply deleted my range names and re-entered them, this time specifying the scope to the current worksheet.

Excel automatically prepends the worksheet name to the range name, in the same style as you indicate above: Sheet1!rnnX.

But I'm confused as to the next steps. If I attempt to copy sheet 1 to sheet 2, the range names still refer to sheet 1. If I go to sheet two and delete the named ranges, this deletes the names from sheet one.

Thanks for any further help.

Update: I'm using Excel 2007. It has no "insert > name > define" function but there is a "Name Manager."

That's why we ask people to put their Excel version in their profile.

If I attempt to copy sheet 1 to sheet 2, the range names still refer to sheet 1.

Don't copy data, copy the whole sheet; e.g., drag the worksheet tab while holding the Ctrl key. Or Home tab, Cells group, Format, Move or Copy Sheet ...