[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

Excel Complex Graphic version 2

I have a great little graphic that is pretty complex and written by Teylyn several months ago.  I'm having difficulty using it because it will not allow me to plot a single or multiple set of categories without plotting all of them.  When I plot all categories, the chart behaves correctly.  When I pick 2 or 3 or 4, the pointers, text and arrows do not line up.  Is there something that can be modified in order to make this work with each category?

Thank you,

B. Complex-Gap-Chart-v4.xlsm
Problem-example-in-Complex-Chart.jpg
0
Bright01
Asked:
Bright01
  • 3
  • 2
1 Solution
 
Arno KosterCommented:
It would be nice if you can give some more information, eg. how you want to select categories etc and supply the password with which the file is protected.
otherwise, in general we cannot help you that much
0
 
Bright01Author Commented:
akoster,

My bad.  I've attached a new version with no password.  Very sorry.  

The way this is used is that data is sent from another sheet i.e. Columns E and F on the Calc sheet and simply populates Columns R and S on the second sheet.  If you look at the jpeg I included, you will see what you get if you do not populate the entire sheet.  I'd like to be able to populate any Category and have it show up properly in the graphic.

Make sense?

Thank you,

B.
Complex-Gap-Chart-v5.xlsm
0
 
Arno KosterCommented:
That makes sense.

Looking at the formula's, i notice that the coloured bars remain as they are and that the arrows move when a category is not populated.
The reason for this is fairly simple :

the arrows are drawn from cell ranges R6:R17 to cell ranges Y6:Y17.
When not all categories are populated, the W column gets the value -0,1
because of this negative value, the axis on which the arraows are drawn move to be able to show this negative value.
when you set the negative value to 0 the arrows line up again.

This can be fixed by changing the formula of W6:W17 to

=max(0; T[x]-0,1)

eg. formula of W7 : = max(0; T7-0,1)


0
 
Bright01Author Commented:
Great job!  I would never have figured that out.  Much thanks.

B.
0
 
Arno KosterCommented:
You're welcome !
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now