How do I sort parent rows without changing child rows?

CarrieSmith

In my sheet, each parent row is a different employee. Each employee has child rows for each month of the year and a YTD row that contains KPI data for each moth. When entering in data, it is helpful for me to sort the parent rows by employee names and then switch to sorting by employee numbers, based on where I am sourcing the data. When I sort the parent rows, I find that the month child rows change order messing up the chronological order in our dashboard that is connected to the sheet and the formulas for the YTD rows.

How do I sort the parent rows without changing the order of the child rows?

James Keuning

Create a field called sort, and put this formula in it:

=IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, [Primary Column]@row)

If you have a date column, you can use this formula to force the children into date sort order also:

=IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, [Primary Column]@row, IF(Sort@row = 1, YEAR([Date Field]@row) + RIGHT(("00" + MONTH([Date Field]@row)), 2) + RIGHT(("00" + DAY([Date Field]@row)), 2)))