Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Excel Macro or Formula needed to create real-time (color coded) vertical chart

    Hello,

    I am looking for an Excel macro/formula that will:

    1. Read the data (book/chapter/section) from one sheet of the spreadsheet.
    2. Display that data in tree form (like an org chart or family tree) on another sheet (tab) in the spreadsheet.
    3. Color code the tree blocks with yes = green and no = yellow

    I have provided a screen shot of what I hope to get out of the real-time excel tree chart updates.

    Any thoughts on to make this happen, is very much appreciated.

    Note, I have done some experimenting with the various Pie Charts, but in this case, I need a top down color coded tree (like an org chart or family tree).

    Thanks in advance.

    I am using the latest version of Excel.

    Jim
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Jim

    In the attached file, the source data sheet is named [data]
    (if you want to rename this sheet, don't have any spaces in the sheet name)
    There are no macros in the file, we just use formulas.
    (We could use macros if required)

    The automated tree sheet is named [tree]
    On sheet named [tree], we can use column [A] to define which columns we are 'fetching' info from sheet [data]
    (we can hide column [A] if required)

    The 'boxes' used for the tree are simply 'merged cells' with an outline.

    Within these boxes, we use 'indirect' formulas to 'fetch' the data from the source sheet [data]
    We can use a 'helper' column [A] on the [tree] sheet to make this easier.

    The formula also converts the source text to UPPERCASE.

    These 'merged boxes' are initial set to have yellow backgrounds, but then have conditional formats applied which 'turn them green' if the corresponding entry is a Y
    (this value is also retrieved via an indirect formula, defined alongside in column [A] )

    If you need to extend the tree, simply copy the named range block1 and paste it below as many times as required for new tree boxes.
    The advantage of using the indirect formulas is that you can insert and delete rows on the [data] sheet, which will be reflected automatically in the [tree] sheet.

    zeddy
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts
    HI Zeddy,
    Thanks for the reply. This sample you sent is fantastic. I tried adding a quick new made up entry and set it to Y and it came in green.
    This is very nice indeed. I will look at it in more detail when I have a free moment. My overall goal is to get away from doing this manually in Visio - especially
    when all the data is in Excel and Excel can be automatically created in tree format.

    My next, question deals with adding branches (sub-branches) off the main branch. Also, what about the main root (top) of the tree. How can that be added in on the tree tab? See attached and thanks once again for looking at this. It looks really nice.

    Jim
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Jim

    Thanks for your kind words.
    I'm currently packing for a trip to London, will be back mid next-week.
    If you haven't had any replies from others by then, I will do another update for you when I get back.

    zeddy

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    James,

    An alternate approach to using formulas as Zeddy did in his EXCELLENT spreadsheet is the VBA approach which is allows a more dynamic and fully automated application than formulas can provided alone.

    Consider the following spreadsheet with routines that will enable you to have 4 levels of hierarchy in your tree: Book, Chapters, Sections, and Subsections. There may be only one book but you can have as many chapters, sections, and subsections as you like. Each Chapter will have its own branch from the Book (top level) while the lines are drawn automatically to connect them. The code also checks to make sure the tree is formatted properly and indicates where the error lies if present. For example, if you have a Chapter and a Subsection listed with no Section in between, you will be notified of what, where, and how to correct it. As you indicated, the boxes will turn yellow or green on a Y/N and will accept a y/n as well. See the images below

    The following tree configuration
    Tree1.png

    produces the following tree
    Tree2.png

    To run the code first make sure the Tree sheet is clear by clicking the "Delete Tree" button on the data sheet. If you forget and try to overwrite an existing tree, you will be notified. Then make sure that you have any cell within the current region selected (The rectangular area that encompasses all cells that are used) on the data sheet and then click the "Build Tree" button. The hierarchy and lines will be drawn, the data populated in the textboxes, and the Y/N validated by coloring the boxes.

    If you decided to use this method and need additional assistance, please let me know.

    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2014-11-15 at 22:10.

  6. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    Nicole545 (2014-11-15),X_LD (2014-12-13)

  7. #6
    New Lounger
    Join Date
    Nov 2014
    Posts
    21
    Thanks
    16
    Thanked 1 Time in 1 Post
    WOW! That is brilliant Maudibe!

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Here is the final revision that adjusts the line to the correct position when outdenting from a Subsection to a Section,
    Attached Files Attached Files

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    X_LD (2014-12-13)

  10. #8
    New Lounger
    Join Date
    Nov 2014
    Posts
    21
    Thanks
    16
    Thanked 1 Time in 1 Post
    Maudibe,

    I am currently using your tree program for the mapping of a project to delegate tasks and it works fantastic. I have 2 questions:

    In a recent thread to a question I posted, you provided a solution for another part of my project creating a mouseover on a textbox. http://windowssecrets.com/forums/sho...-for-textboxes
    Can you do the same with the textboxes created in your tree?
    Private Sub TextBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If X > 5 And X < TextBox1.Width - 5 And Y > 5 And Y < TextBox1.Height - 5 Then
    ActiveSheet.Shapes.Range(Array("Rectangle 1")).Visible = True
    Else:
    ActiveSheet.Shapes.Range(Array("Rectangle 1")).Visible = False
    End If
    End Sub

    Could your code be modified to set up simplified workflow charts like Visio? Here is a sample of the workflow I am working on.

    Workflow.png

    Thanks a million
    Nicole

  11. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Nicole,

    The answer to your first question is that I guess it is possible. I would have to give it more thought on how to write the code since the textboxes are created on the fly. As far as the second answer, I guess it is possible to modify the code to produce simple flow charts but the coding would be very complicated. It would only scratch the surface of what Visio is capable of doing. At this point in time, I would not have the time to devote to such a project although a challenging thought.

    Maud

  12. #10
    New Lounger
    Join Date
    Dec 2014
    Location
    Vancouver, BC
    Posts
    19
    Thanks
    18
    Thanked 0 Times in 0 Posts
    I am clicking on the build tree and nothing happens.

  13. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    X_LD,

    Make sure you downloaded Tree_2,xlsm. Select the book, a chapter, a section, or subsection on the data sheet and then click the build tree button. The tree will be built on the Tree screen.

    Add new chapters, sections, or subsections to the data sheet. Delete the old tree using the provided button then build a new tree reflecting the newly added sections. You can copy the Tree sheet and paste it into another application such as a PowerPoint or save it as a PDF or a web page.

    HTH,
    Maud

  14. The Following User Says Thank You to Maudibe For This Useful Post:

    X_LD (2014-12-13)

  15. #12
    New Lounger
    Join Date
    Dec 2014
    Location
    Vancouver, BC
    Posts
    19
    Thanks
    18
    Thanked 0 Times in 0 Posts
    That is so AWESOME!!! This will be saving me a lot of time! I love the connecting lines that automatically draw. Will never know in my lifetime how you did that. Any updates forthcoming to go deeper than 4 levels?

    Brian

  16. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Thanks Brian. Actually Adding another level would be a matter of just duplicating the code with a few changes. The problem lies in that to get another level in, many changes to the horizontal positioning of the objects/lines would be required. Since the OP has not responded, the answer is not at this time. But thanks for the inquiry.

    Maud

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •