Dynamic chart with 2 axis

[Akash kothari]

Hello Friends,

I am trying to make a column chart with 2 axis.
Have attached a sample file with this mail.
Using offset function with datavalidation

Below graph is the one i am looking for in the attached file.

Thank you
Akash

 

[Hui]

Akash

The current chart is linked to another set of data
Do you want it linked to the data above it?

When you say a second axis what do you mean, because you don't need a second axis top produce that chart

What about the other two sets of data, what do you want done with them ?

The attached file allows you to select the set of data you want and the chart updates

 

[Luke M]

I think you meant 2 series, not 2 axis. Is this what you wanted?

 

[Akash kothari]

Hi Hui,

Thanks for you reply,

1)Yes, i want to link it with above table.
2) Secondry axis because there are 2 set of information, if you have any other way you can bring "Ontime" & "Late Number" (From B4 to G4) in the same chart i am fine with that.
3) Other set of data should also get change in the same chart. when i use the data validation drop down.(when i select management it should reflect Management Ontime & Late, If Brussels Brussels "Ontime & Late" should come.

I want the "Ontime & Late" to reflect in the given chart format but when i am trying to use offset and data validation is giving Error as (more than 1 cell or column).

Thank you
Akash

 

[Akash kothari]

I think you meant 2 series, not 2 axis. Is this what you wanted?
View attachment 9588

Yes, You are correct but it should be dynamic using List data validation drop down for all other location.

 

[Hui]

If you download your file from my post above it does that !

 

[Luke M]

I would echo Hui's statement. Both uploaded files have dynamic charts.

 

[Akash kothari]

Hi Hui/ Luke,

Sorry for delayed responce,

Yes that file works perfect. Thank you so much

Would you mind pointing out my mistake where i was doing wrong?
and I can add few more location on next blank columns ? right.

Thank you
Akash

 

[Hui]

Akash

I use Named Formula in my example
If you got the Name Manager,
got Formulas, Name Manager tab
Select either Late or OnTime
You will see the formula is like:
=OFFSET(Sheet1!$B$5:$B$10,,MATCH(Sheet1!$A$2,Sheet1!$B$3:$G$3,0))
To add more data simply extend the $G$3 to your last column eg:$X$3
same with the other formula

 

[Luke M]

Rather than having the dynamic range feed the chart, I setup some cells to dynamically pull from your data. This was the first step. The other part was that in your waterfall, the on time + late should always equal 100. This meant that we needed to do a little math to adjust the numbers correctly, so that they stack/line up correctly. You should be able to add more numbers. Everything in the formula cells is based off of the Named Range. Since you already know how to set that up, should be able to add more columns fairly easily.

Or, use Hui's method.

 

[Akash kothari]

Thank you Hui & Luke

Akash