Goal: Create a set of charts on Excel worksheet, copy the worksheet, change the country name, have the charts' data sources update automatically.
I'm fairly confident this can be done. I've tried searching (both here and the broader general) but I keep finding answers about using table names or range names so a chart will update to include any new data. That's not what I am after. I need to create the same set of 10 charts for 10 different countries. I have identically-formatted data sheets for each country (the sheet name is simply the country name). I am trying to avoid having to manually update the data source information 100 times. Instead, I would like to find a way to use a cell reference with the country name. Then I can create a sheet with the charts for the first country, copy that sheet, update the cell to a new country name, and have all the charts update using the new country's data. Possible? I know that you cannot use INDIRECT in the chart data source.
I'm fairly confident this can be done. I've tried searching (both here and the broader general) but I keep finding answers about using table names or range names so a chart will update to include any new data. That's not what I am after. I need to create the same set of 10 charts for 10 different countries. I have identically-formatted data sheets for each country (the sheet name is simply the country name). I am trying to avoid having to manually update the data source information 100 times. Instead, I would like to find a way to use a cell reference with the country name. Then I can create a sheet with the charts for the first country, copy that sheet, update the cell to a new country name, and have all the charts update using the new country's data. Possible? I know that you cannot use INDIRECT in the chart data source.
