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

Topic

New Reply

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

Reply | Quote

Replies

Hi Jim

In the attached file, the source data sheet is named [B]data[/B]
(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 [B]tree[/B]
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 [B]tree[/B] 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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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
38415-Tree1

produces the following tree
38416-Tree2

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

Reply | Quote

WOW! That is brilliant Maudibe!

Reply | Quote

Thanks!

Reply | Quote

Here is the final revision that adjusts the line to the correct position when outdenting from a Subsection to a Section,

Reply | Quote

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/showthread//165438-Instructions-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 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.

38473-Workflow

Thanks a million
Nicole

Reply | Quote

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

Reply | Quote

I am clicking on the build tree and nothing happens.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote