Multilevel tree data

J'ai posté en anglais ci-dessous alors qu'il s'agit d'un forum francophone; Veuillez m'en excuser - J'espere qu'un anglophone de bonne volonté pourra m'aider; dans le cas contraire, je peux traduire mon (long) post en anglais ci-dessous...



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:



No problem Scilla, welcome in sheets-pratique.

However, access to your spreadsheet is denied. Please share as follows (you may be able to find help in English on the web).

Many thanks; I think this new link should work:

I will edit the initial post with this new link as well.

ok, now I will try to understand your project ! (may be this evening or tomorrow morning)

What I suggest is :

  • Build a new tab called "Tables" in which we will find the valid terms depending to the choices made in tab "Navigation". Theses choices are determined by a formula at each step as for instance :
=sort(unique(query(Data!A:G,"select G where A='" & Navigation!A2 & "' AND B='" & Navigation!B2 & "' AND C='" & Navigation!C2 & "' AND D='" & Navigation!D2 & "' AND E='" & Navigation!E2 & "' AND F='" & Navigation!F2 & "' ",0)))
  • Build a new tab called "Navigation" in which you can find data validation in cells row#2
  • If a change of one choice has been done, I erase all the choices made on the right by this script
function onEdit(event){
  var feuille = event.source.getActiveSheet();
  var cellule = event.source.getActiveRange();
  if (feuille.getName()=='Navigation' && cellule.getRow()==2){


in A6 of "Navigation"

=IFERROR(sort(unique(query(Data!A:H,"select H where A='" & Navigation!A2 & "' AND B='" & Navigation!B2 & "' AND C='" & Navigation!C2 & "' AND D='" & Navigation!D2 & "' AND E='" & Navigation!E2 & "' AND F='" & Navigation!F2 & "' AND G='" & Navigation!G2 & "' ",0))))

Hope that is clear and fit to your request. Feel free to give me your feedback.

This is great! Many thanks Mikhail

Good news.

If everything is ok, close the thread by clicking

Done; thanks again

Rechercher des sujets similaires à "multilevel tree data"