Excel Complex Graphic version 2

Posted on 2011-05-09
Last Modified: 2012-06-21
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
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.
    otherwise, in general we cannot help you that much

    Author Comment


    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,

    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)


    Author Closing Comment

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

    LVL 19

    Expert Comment

    You're welcome !

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    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 …
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now