09-08-2019 10:56 PM
09-08-2019 10:56 PM
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.
For example, let's take the chart series formula
where my last column of sales data is BZ.
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)
Thanks for any help in advance.
09-08-2019 11:56 PM - edited 09-09-2019 01:33 AM
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:
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.
Switching Charts using Index & Match
Switching Charts Using an Offset Function
Hope that helps
09-09-2019 12:05 AM
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.
09-10-2019 01:35 PM
@huw_jones One option is to set up a chart that has a dynamic range that you can control by specifying which data is included in the chart. I wrote an article with detailed instructions for this at https://www.thinkoutsidetheslide.com/how-to-create-an-excel-chart-with-a-dynamic-range-that-you-sele....
Hope that helps.