Solved

# Excel Complex Graphic version 2

Posted on 2011-05-09
402 Views
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
Question by:Bright01

LVL 19

Expert Comment

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.
0

Author Comment

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

LVL 19

Accepted Solution

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

Author Closing Comment

Great job!  I would never have figured that out.  Much thanks.

B.
0

LVL 19

Expert Comment

You're welcome !
0

## Featured Post

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …