Hierarchy Rollup


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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Jan Karel PieterseExcel and VBA ExpertCommented:
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.
ablove3Author Commented:
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.
Jan Karel PieterseExcel and VBA ExpertCommented:
I couldn't come up with a faster function unfortunately.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ablove3Author Commented:
No problem, thank you.
I suggest starting with this BOM hierarchical rollup Access article

and this tree/hierarchy article
Take a look at the attached database and the queries that read a table, imported from your workbook.

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:
Hi Aikimark, I'll have to look at this tonight or tomorrow, but I'll have a response back to you by this weekend.
no rush
ablove3Author Commented:
Using Access Database is the best solution
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.