Hi,
I created a google sheet with the data from a multilevel tree and looking for ways to easily navigate/ access the data.
Data structure : my current tree has 8 levels (1 level per column); the first 7 levels are 'objectives' and some 'solutions' are displayed at level 8; for instance: Level 1 objective is to 'Increase profitability', to achieve this objective there are various objectives at level 2 like 'Reduce operating costs' or 'Increase profitable business', then these level 2 objectives are sub-divided into level 3 objectives etc. until we reach a number of 'solutions' at level 8.
Also, each solution is categorized e.g. 'Process' solution, 'Technology' solution etc.
Finally, the solutions can be applicable to multiple objectives e.g. solutions A, B and C can be applicable for objective X (which is a level 4 objective), AND also applicable for objective Y (which is for instance a level 6 objective); when I met this type of situation, I did not copy the 'solutions' for all applicable objectives as I want to minimize the number of rows and cannot maintain multiple lists of identical solutions as this would be very difficult to manage over time; as a workaround, I only indicated 'See: Objective X' as a 'solution' when needed.
At this stage, I managed to create a pivot table which displays the different objectives in rows, the categories of 'solutions' as columns and the unique 'solutions' as 'values'; unfortunately, this is difficult to read / use as there are many levels, objectives and solutions.
I tried using 'filters' and'slicers' but this is still not user friendly; I'm looking for a better solution like a basic form maybe where one could select an 'objective' (at level 1, 2,...7), see what sub-objectives are available (if any) and ultimately see all the solutions (by category) which are available to achieve the selected objective(s).
Ideally, this form could also show to what upstream objectives a selected objective (or solution) is contributing to, but this is more a secondary goal for me; like the cherry on the cake ;)
I considered using some kind of relationship database but I do not have the skillset to do that and thought there might be an easier solution directly in Google sheet.
Sorry for the long post; just wanted to be clear - I would appreciate any help; you can find an extract of my data sheet and draft pivot here:
https://docs.google.com/spreadsheets/d/11j4YoKNRkSSyigvhXUS1zZcg6DhT4FRrjF0ZhbssW2M/edit?usp=sharing
Thanks;
Syl