Alright, I thank you @QHarr for your help.
In the end, non of those were what was needed as an answer, but I have found the way.
I created, on a separate sheet called “PivotTable”, a table based on cells with the following (and similar) formulas:
=INDEX(Metrics!$B$2:$ZZ$2,1,Metrics!A57,1)
“B2:ZZ2”, had their numbers changed in each cell from left to right, to point to one column further to the right, so each time I pulled with them a range one cell further to the right.
“A57” and onward, I placed a list of numbers in cells starting at “1” in cell A57″ of sheet “Metrics”. Just to use for this formula.
Whenever I add a new column to the right of the original table in “Metrics” sheet, I can go over to my “PivotTable” sheet, and drag the last row for Excel’s auto-complete to create the new row in the “pivoted table” with the proper values from the new column.
Then I went to my charts and based the formula for the values on the following:
=PivotTable!$B$2:$B$4
“B2:B4”, these are the cells in the column of the pivoted table that I want to use for the particular chart column/line.
Whenever I use the auto-complete method mentioned above on the pivotted table, the chart will automatically be updated accordingly.
This is far less elegant than I would have liked, but it works well, and most importantly(!) requires little work from whomever needs to update the excel.
They just enter the values in the original table, and drag the row on the pivoted one.
—
Ziv.
solved How do I dynamically reference headers in Excel