Solved

Hierarchy Rollup

Posted on 2014-09-23
11
114 Views
Last Modified: 2014-10-17
Hello,

I need some help with the attached spreadsheet.  Columns D:M, creates my hierarchy rollup with Employee ID numbers.  Columns W:AF are the names associated with the employee ID#'s.  This rollup is from the employee up to the CEO.

Columns AG:AP is the hierarchy from the CEO down to the direct manager.  I've added a lot of formulas to make this happen, but whenever I need to refresh the data, it takes about 2 hours out of my to do this process.  It stalls from 15-20 to recalculate.

Is there another to get the Bottom Up and Top Down hierarhy that would require a simplier formula or a macro  I have to refresh 3 of these twice a week and waiting on the form to save or calculate is very counter productive

===============
Related question: http://www.experts-exchange.com/Q_28522751.html
Hierarchy-Rollup--example-.xlsm
0
Comment
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
  • Learn & ask questions
  • 4
  • 3
  • 2
11 Comments
 
LVL 11

Expert Comment

by:jkpieterse
ID: 40341024
If you could elaborate on how exactly the UDF works, perhaps we can come up with a native-formula way of doing this, or -alternatively- a VBA method to do them all in one go instead of using a user defined function.
0
 

Author Comment

by:ablove3
ID: 40341572
Someone offered me a different solution to use a more powerful data base and I completely agree, but this is a short term project through January and I don't feel that my company will expense it.  I used Excel because there are often missing managers or managers that loop, because they report to themselves in our ERP and I have to use some logic to find the true manager.

I started this project, by using the employee ID of the employees and their managers to build a hierarchy from bottom up, meaning their direct manager, the manager's manager and so forth until it leads up to the CEO of the company.  I then used a lookup to look up each layer by name from their employee ID, so I could see in name form how the hierarchy looks.  Then I was provided with a formula that would show "top down" mean from the CEO (level 1), to the CEO direct reports (level 2) all the way to the direct manager of the employee.  We need that view in order to determine who the level 2 managers are and every level down from there.
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 40341850
I couldn't come up with a faster function unfortunately.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:ablove3
ID: 40342244
No problem, thank you.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40342404
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
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40342664
Take a look at the attached database and the queries that read a table, imported from your workbook.
Hierarchy.accdb
0
 

Author Comment

by:ablove3
ID: 40344202
Hi Aikimark, I'll have to look at this tonight or tomorrow, but I'll have a response back to you by this weekend.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40344220
no rush
0
 

Author Closing Comment

by:ablove3
ID: 40387917
Using Access Database is the best solution
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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…

691 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