Solved

# Org Hierarchy Help

Posted on 2014-09-21
Medium Priority
192 Views
I have a hierarchy rollup which deplays management bottom up and top down.

The top down managers are in columns of levels
Level 1 CEO
Level 2 CEO direct reports
continuing to Level 10

I need someone to look at what I've done to see if they can make this process simplier

An I need for the managers in Column T to show their level in Column U

EXAMPLE
Column T                      Columnn U
CEO 1                             LVL 1      (U3) from AG
Manager 13                   LVL 6      (U101) from AL
Manager 521                 LVL 10    (U7973) from AP
Manager (EXEC) 160     LVL 4      (U5350) from AJ
ROLL-UP--example-.xlsx
0
Question by:ablove3
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 10
• 9

LVL 46

Expert Comment

ID: 40337376
Do you need to retain those lookups or are you only concerned with the LVL # values in column U?
0

LVL 46

Expert Comment

ID: 40337451
I put the following formula in U4 and did a fill down operation to populate the cells below.
="LVL "&11-COUNTBLANK(D4:M4)
0

Author Comment

ID: 40337725
I need to obtain the lookups because the data will be refreshed 2 twice a week.

I like your formula and I see what it does, but I need it to identify the level for the employee in column T.

The CEO 1 is the only "LVL 1" (view the filter for column AG)

President 5 is a LVL 2 (view the filter for column  AH)
0

LVL 46

Expert Comment

ID: 40337795
Did you incorporate the formula as I instructed you to do?
0

Author Comment

ID: 40338094
Yes, I followed your instructions and here are the results (attached).

What I'm looking for is a form of lookup on the cells in column to in the range of AG:AP that will return a column header from AG\$2:AP\$2 for the values in column T.  It looks like your formula is returning the column header for the values in U
0

Author Comment

ID: 40338108
Copy-of-ROLL-UP--example-.xlsx
0

LVL 46

Expert Comment

ID: 40338270
I'm filling in the LVL # values.  That is what you stated in your question.
0

Author Comment

ID: 40338688
Yes, you are, but you're not bringing in the value for the right cells.  I don't think you read my question in great detail. I gave you the values that I expect to see in three different cells.  If a name in column T doesn't appear in the names between AE:AN, it shouldn't receive a level.  Thank you for your effort, but I'll leave the question open for a little while.
0

LVL 46

Expert Comment

ID: 40338897
AE:AN
did you mean AG:AP?
0

Author Comment

ID: 40339062
Yes, I've been working with so many spreadsheets,, I'm getting them all confused lol!
0

LVL 46

Accepted Solution

aikimark earned 2000 total points
ID: 40339560
This function does the lookup.  However, you will need to use an XLSM or XLSB format for your workbook.
Public Function GetColHdrForMatch(parmFind As String, parmRange As Range) As String
Static dicRanges As Object
Static dicRng As Object
Static rng As Range

If dicRanges Is Nothing Then
Set dicRanges = CreateObject("scripting.dictionary")
End If
Else
Set dicRng = CreateObject("scripting.dictionary")
For Each rng In parmRange.Cells
If Len(rng.Value) <> 0 Then
If dicRng.exists(CStr(rng.Value)) Then
Else
dicRng(CStr(rng.Value)) = rng.Column
End If
End If
Next
End If
If dicRng.exists(parmFind) Then
GetColHdrForMatch = parmRange.Worksheet.Cells(parmRange.Row, dicRng(parmFind))
Else
GetColHdrForMatch = vbNullString
End If
End Function

To use this in your worksheet, place the following formula in U3 and do a fill-down operation.
=GetColHdrForMatch(T3,\$AG\$2:\$AP\$23852)
0

Author Comment

ID: 40340686
aikimark, I'm not sure what I'm doing wrong.  I added the code and saved the file as xlsm. When I put the formula in column U3, I get the error message "#Name?"

What am I doing wrong?

I didn't realize you were a coder.  If you have time could you see if there's a way to streamline the hierarchy bottom up (W:AF) and Top down (AG:AP).  Those formulas are killing my excel space.  Everytime I save it takes 20 minutes for the worksheet to save.  I lose 2 hours of time a day, just waiting several times for the form to save.  I also have to do 3 of these for different scenarios.  I desperately need a time saver.
ROLL-UP--example-2.xlsm
0

LVL 46

Expert Comment

ID: 40340699
Where did you put the routine?  It ought to go into a module, which you will need to insert.
0

Author Comment

ID: 40340728
hum, that's strange, when I opened the file back up, it seems to work now.  That's awesome. Thank you.
0

Author Closing Comment

ID: 40340730
I received an excellent solution that all help me save a lot of time at work.
0

LVL 46

Expert Comment

ID: 40342411
I suggest starting with this BOM hierarchical rollup Access article
http:A_19-BOM-parts-list-expansion.html

and this tree/hierarchy article
http:A_2153-Managing-Trees-and-Hierachies-in-Access.html
0

Author Comment

ID: 40342452
ok, I'll try them
0

LVL 46

Expert Comment

ID: 40342472
You can disable automatic calculation as well as calculation before saving.  These are both options under your control.
0

Author Comment

ID: 40343876
Great idea.  Thank you
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month9 days, 3 hours left to enroll