jb_excel_123
New Member
- Joined
- Nov 18, 2021
- Messages
- 4
- Office Version
- 365
- Platform
- Windows
Hi all,
I'm trying to create a set of dynamic charts that will change when I update a dropdown (that has a named range). I've set up the set of charts using a series of named ranges that are broadly formulae that do some sort of dynamic OFFSET to get a range series.
Within the template sheet I can make changes and all the charts will update accordingly, and ahe idea is that I'll be able to replicate this worksheet several times and on each chart just update the dropdown to update all the charts.
The issue I have is that when I copy the worksheet, any charts that are using a named range are retaining the references to the original sheet's dropdown.
I suspect that it's something to do with how the cells are referencing the dropdown-range, but I've tried updating the dropdown-range's scope to be either workbook or the worksheet, and the range in the chart just sticks with whichever range it had when copied.
I'll attempt to show with pictures:
The fund code referenced dictates the data that populates the charts, then the charts are populated using the named ranges:
Then I copy the sheet to a new worksheet, but when I look in the chart I simply get the reference to the previous tab:
The tracking error that I'm referring to is a named range that is referencing the dropdown. In theory this should update the chart with the new data but it doesn't seem to be.
Is there a simple way for this range to update to whichever sheet that it's in, or do I have to manually adjust each of the charts to show the specific worksheet's range?
I'm happy to share more information but didn't want to over provide initially.
Thanks in advance!
I'm trying to create a set of dynamic charts that will change when I update a dropdown (that has a named range). I've set up the set of charts using a series of named ranges that are broadly formulae that do some sort of dynamic OFFSET to get a range series.
Within the template sheet I can make changes and all the charts will update accordingly, and ahe idea is that I'll be able to replicate this worksheet several times and on each chart just update the dropdown to update all the charts.
The issue I have is that when I copy the worksheet, any charts that are using a named range are retaining the references to the original sheet's dropdown.
I suspect that it's something to do with how the cells are referencing the dropdown-range, but I've tried updating the dropdown-range's scope to be either workbook or the worksheet, and the range in the chart just sticks with whichever range it had when copied.
I'll attempt to show with pictures:
The fund code referenced dictates the data that populates the charts, then the charts are populated using the named ranges:
Then I copy the sheet to a new worksheet, but when I look in the chart I simply get the reference to the previous tab:
The tracking error that I'm referring to is a named range that is referencing the dropdown. In theory this should update the chart with the new data but it doesn't seem to be.
Is there a simple way for this range to update to whichever sheet that it's in, or do I have to manually adjust each of the charts to show the specific worksheet's range?
I'm happy to share more information but didn't want to over provide initially.
Thanks in advance!