andydtaylor
Active Member
- Joined
- Feb 15, 2007
- Messages
- 360
- Office Version
- 2016
Hi,
I'm seeking to effect the most robust way a user can to drill through 57k rows of organisational hierarchy in a cascade style.
<ul>
<li>My ask is to create a tool for users to log business services. So it needs to be quick and robust and intuitive</li>
<li>There are 8 levels of hierarchy from Legal Entity down to cost center</li>
<li>For any given service I want to capture the code of the org unit(s) providing and org unit(s) receiving</li>
<li>"Org unit" can describe the code of any of the 8 hierarchy levels</li>
<li>Codes at any given org level are unique, but all occurrences of any given n+1 department code are common across LEs where that department exists within a given LE</li>
</ul>
What's the best way to do this?
<ul>
<li>For capturing the information I propose an excel table I can pick up later in bulk and make sense of with Power Query. I would like the user to log rows where the first column is "Side" i.e. provider/receiver and then fill out columns to the right as required which describe hierarchy (LE, BD, BU etc)</li>
<li>For presenting input to users I am aware of in-cell validation and also wondering if a pop-up excel form might be of genuine use here</li>
<li>Going round in circles at the moment with Power Query summaries of the 57k row hierarchy and indexed slices versus dynamic named ranges. I was hoping to effect something elegant and simple using a column name/cell value driving data validation to a table name. But I feel like I need to jump to a table and filter it</li>
</ul>
A steer on the above would be much appreciated. If I can do it in Excel then I want to do it that way. If I need to do it in Access I will but this is considered witchcraft around here and I remember the front end form stuff being a bit of a pig...
Many Thanks,
Andrew
I'm seeking to effect the most robust way a user can to drill through 57k rows of organisational hierarchy in a cascade style.
<ul>
<li>My ask is to create a tool for users to log business services. So it needs to be quick and robust and intuitive</li>
<li>There are 8 levels of hierarchy from Legal Entity down to cost center</li>
<li>For any given service I want to capture the code of the org unit(s) providing and org unit(s) receiving</li>
<li>"Org unit" can describe the code of any of the 8 hierarchy levels</li>
<li>Codes at any given org level are unique, but all occurrences of any given n+1 department code are common across LEs where that department exists within a given LE</li>
</ul>
What's the best way to do this?
<ul>
<li>For capturing the information I propose an excel table I can pick up later in bulk and make sense of with Power Query. I would like the user to log rows where the first column is "Side" i.e. provider/receiver and then fill out columns to the right as required which describe hierarchy (LE, BD, BU etc)</li>
<li>For presenting input to users I am aware of in-cell validation and also wondering if a pop-up excel form might be of genuine use here</li>
<li>Going round in circles at the moment with Power Query summaries of the 57k row hierarchy and indexed slices versus dynamic named ranges. I was hoping to effect something elegant and simple using a column name/cell value driving data validation to a table name. But I feel like I need to jump to a table and filter it</li>
</ul>
A steer on the above would be much appreciated. If I can do it in Excel then I want to do it that way. If I need to do it in Access I will but this is considered witchcraft around here and I remember the front end form stuff being a bit of a pig...
Many Thanks,
Andrew
Last edited:
