Hi, I’m trying to make the x-axis on a graph variable based on data in a field. ie if the field contains the date 2030 then I would like the x-axis to go to this date, but if that date changes then the graph axis also changes. Essentially I want to expand and contract the range based on a variable.
Any help much appreciated
Toby
Hi Rifkov,
Unfortunately one cannot use formulas within the Data Range specification of a chart. So the answer I see is to use a macro.
I have attached an example spreadsheet with a very simple chart and macro solution. Of course, you will need to convert things to your situation, which will likely mean (if you want to go by years, for example) using some date functions and/or modular mathematics. I think questions on that sort of math would be okay to continue to post here, since date range changes would be a likely scenario for anyone wanting to do variable ranges.
Notice when playing with the example that you have to click off from (or Enter/Tab from) the End Row designation cell before clicking the Reset Chart button if you want to reflect the entry just made.
The macro is fairly clear. However…
- Notice, as remarked in the code, that you must reassign the complete range array to a chart…you cannot just assign to a single element of that array. So, if you had multiple data ranges, you would have to do noticeably more work prepping that array than is done here, where I’ve just wrapped the one and only new data range with Array().
- DataArray(0)(0) just means to get the value of the top left cell in a data range.
ChartRangeViaMacro.ods (22.7 KB)