Org Hierarchy Help

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
ablove3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
Do you need to retain those lookups or are you only concerned with the LVL # values in column U?
0
aikimarkCommented:
I put the following formula in U4 and did a fill down operation to populate the cells below.
="LVL "&11-COUNTBLANK(D4:M4)

Open in new window

0
ablove3Author Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

aikimarkCommented:
Did you incorporate the formula as I instructed you to do?
0
ablove3Author Commented:
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
ablove3Author Commented:
sorry upload didn't complete
Copy-of-ROLL-UP--example-.xlsx
0
aikimarkCommented:
I'm filling in the LVL # values.  That is what you stated in your question.
0
ablove3Author Commented:
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
aikimarkCommented:
AE:AN
did you mean AG:AP?
0
ablove3Author Commented:
Yes, I've been working with so many spreadsheets,, I'm getting them all confused lol!
0
aikimarkCommented:
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
    If dicRanges.exists(parmRange.Address) Then
    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
        dicRanges.Add CStr(parmRange.Address), dicRng
    End If
    Set dicRng = dicRanges(CStr(parmRange.Address))
    If dicRng.exists(parmFind) Then
        GetColHdrForMatch = parmRange.Worksheet.Cells(parmRange.Row, dicRng(parmFind))
    Else
        GetColHdrForMatch = vbNullString
    End If
End Function

Open in new window


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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ablove3Author Commented:
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
aikimarkCommented:
Where did you put the routine?  It ought to go into a module, which you will need to insert.
0
ablove3Author Commented:
hum, that's strange, when I opened the file back up, it seems to work now.  That's awesome. Thank you.
0
ablove3Author Commented:
I received an excellent solution that all help me save a lot of time at work.
0
aikimarkCommented:
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
ablove3Author Commented:
ok, I'll try them
0
aikimarkCommented:
You can disable automatic calculation as well as calculation before saving.  These are both options under your control.
0
ablove3Author Commented:
Great idea.  Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.