I've created a spreadsheet which tracks monthly sales of each of the 40 products I sell, and have created line charts for each product showing sales by month.
Each month I add a column to the right of the last data column and add the new month sales.
Is it possible to add a variable in the chart series formula where the variable refers to a specific reference cell, so I can just change the value in the cell and all the charts will automatically update? At the moment I manually edit each series formula in each chart with the latest column reference.
I then add the latest month's sales data in column CA.
What I would ideally like is.... in the reference cell (A1 for example) I change the input from "BZ" to "CA" and this (somehow!) automatically updates all "BZ" references in all the chart series formula to "CA". So the formula above becomes =SERIES(Data!$F$101,Data!$L$79:$CA$79,Data!$L$101:$CA$101,4)
If your goal was just to have the source data for the chart to expand and include newly added rows or columns then just convert the source list to a table. There are so many ways of converting a List to a Table (Auto-expandable) the easiest is by using the shortcut CTRL + L or CTRL + T.
However, my understanding is you have a chart for the different products (row labels) and you want to be able to pick up a Different Month (months are in column labels) and have the chart automatically reflect your selected column. then, your goal of dynamically changing the source data Column for a chart can be done in 2 ways:
Creating a preparation table using Index & Match functions
Using an Offset function to dynamically pick up the correct input range.
I have a preference in your situation to using an OFFSET function.
Because of the extensive details that cannot be demonstrated by writing the steps, here are 2 detailed video tutorials I created one for each option. Please watch them and let me know which method works best for your specific work situation.
By the way, we use both techniques for creating Dynamic Dashboards.
Other workaround would be, convert your Data into an Excel Table and then insert the Chart based on the Table data itself so that next time when you add another month column in the Table for the next month or you add more data down the rows, the Chart will be updated automatically.
To convert your data into an Excel Table, select all your data including headers and press the shortcut key Ctrl+T and make sure that the CheckBox which says "My table has headers" is checked in the next window which pops up next and click OK to finish.