To be able to toggle the data by 2 fields to display graph

[michael35]

Hi All,

I need some help on a graph chart I am working on. On sheet1 will be where i have a graph chart showing there series of costs for location 1, location 2 and the total costs for both location 1 and location 2. I have the data for these 3 series over the course of a few years in the sheet named tab. I have the costs tabulated in both a vertical and horizontal table. The issue i really could use help figuring out is on sheet 1, I will need the graph table to be able display to toggle by the below selection. The current graph shows the data for the year 2019 alone and i will need to manually refigure the data to show another year/location. Hope i can get some help on this. I will be really appreciative of it.

Cell Q2 - Location
1. Location 1
2. Location 2
3. Location 1 & Location 2 and Total of both locations

Cell T2 - Year
1. Year 2015
2. Year 2016
3. Year 2017
4. Year 2018
5. Year 2019
6. Year 2020

[Roel Jongman]

I think this will get you close to where you need to be.

you should have a special table that is filled with the year you want to present and link that to the graph and when you change T2 or Q2 then this table gets updated and there is no need to move down the individual ranges the ranges in the graph are always the same.
For my convienience I build that table on sheet1. but can offcourse be on costs sheets as well.
What I did is build a formula that will show the months depending on the year in T2 and the location depending on Q2.

Also to avoid lines running along the X axis for unfilled month I put a forced error N/A in the formulas so the line tops after april as long as a month (in this case june) is not started yet
The graph will however always show all months of a year, filled or unfilled. on the X axis.
Also I set the Y axis to a fixed maximum of 350 so that when you only select 1 location the graph does not resize due to auto sizing because a location has lower value.

please see attachment for the formula and effect on the graph

[José Augusto]

Hi

To complement the good work of @Roel Jongman
Use in Sheet1!C2 (or other) the formula

=T2 & " Total Electricity Costs"

Select the title of the chart and in formula bar use =C2

So, the title are updated when you change the year.

[michael35]

Thank you very @Roel Jongman and @Jose Augusto. It really worked!! Can i check instead of an N/A error can i leave it as a blank. Reason being i would like to display the data labels if possible

[Roel Jongman]

You can change them for Loc 1 and Loc 2 formulas that will then show 0,00 instead of #na.
for the line and total you need the forced error to not have the strange linedrop. but there is no Issue there, it hides both the label and the line..

I did not like the zero's showing for months without values so I was able to format them to hide also with numberformatting.
you can read here how I set it up.. https://www.extendoffice.com/documen...ta-labels.html

Seems like a very presentable chart this way

[michael35]

Thank you!!