Dynamically updating charts and non-sheet specific named ranges?

[PM_ME_YOUR_DATASETS]

Hi everyone, first time posting here. I've been hitting my head against a wall on this particular problem for a few days, and I could use some help. I'm using Excel 2016 and would like to avoid VBA.


I have a bar chart (as an object within a sheet) that uses named ranges and the offset function to update dynamically. For this chart, raw scores are on the x-axis and are in data column A, and the frequency/count of each raw score is on the y-axis and are in data column B. In this case, my defined names look like this:
RawScoreCount:
=OFFSET('6.2.1.1A'!$B$6,0,0,COUNT('6.2.1.1A'!$B$6:$B$400),1)
RawScores
=OFFSET('6.2.1.1A'!$A$6,0,0,COUNT('6.2.1.1A'!$A$6:$A$400),1)

('6.2.1.1A' is the sheet name, and the data range begins in row 6)

In my chart, the sole series is defined as:
='Workbookname'!RawScoreCount
With horizontal axis labels defined as:
='Workbookname'!RawScores

This chart does what I want it to do, and updates correctly if the number of rows of data to be included changes.


However, I'm trying to use this sheet as a base template that I can duplicate and then modify within the same workbook. However, when I copy the sheet, the newly copied chart's data range is no longer dynamic and no longer references a defined name. When I open the Name Manager, new copies of the defined names are being created, but the chart just isn't using them. The new chart's series definition, for example, is instead:
='6.2.1.1A (2)'!$B$6:$B$23

I have tried this with the Named Range scope being set to both 'Workbook' and '6.2.1.1A' to the same result.


Anyone have any idea how to fix this? My plan was to be able to copy (and then slightly modify) this sheet perhaps as many as 150 times within the workbook, so going in and creating a new named range and such for each sheet isn't tenable.


Ideally, what I'd like to have is a Named Range that doesn't use a sheet reference at all, so something like this:
RawScoreCount:
=OFFSET($B$6,0,0,COUNT($B$6:$B$400),1)
That way, the range would only be tied to the sheet it's being used within, and I would only need to use 2 defined names (one for current sheet values, one for current sheet labels) for the entire workbook. I don't know if this is possible though; whenever I attempt to do so the sheet name gets automatically inserted.


Can anyone help me?

 

[PM_ME_YOUR_DATASETS]

Resolved my issue, in an extremely simple way.

The whole time I had been attempting to be able to copy the base template, then to be able to make adjustments on the copied sheet. This wasn't working because copying the chart would cause all of the dynamic ranges in it to become static ranges.

The easiest solution was just to swap the order of operations. The solution was to first make adjustments to the base template, then copy the template. The base template would remain repeatedly adjustable in the future, and the copied sheet would retain whatever adjustments were made at the time of the copy. Problem solved.

Best of luck everyone!