Best Of

Hi @Esquared213

Lets try two different formulas to determine where it's having trouble:

=SUM([Annual Revenue]:[Annual Revenue])

and

=COUNTIFS([Fiscal Year]:[Fiscal Year], 2023)

These should both return a number. If one of them is giving you an error or incorrect result, this will help us narrow down where it's getting stuck!

Is there a reason you are doing it this way instead of turning on the dependencies within the sheet?

@Siobhan16

Great! I also use IFERROR when indexing reason is the #NO MATCH is set by SS and we do not know what value they are using to set it. So when you have #NO MATCH it also prevents you from using conditional formatting. We use a lot of indexing to update over 700 Pharmacy Sites in tons of sheets and when a site closes it is removed from our Reference API so it cannot match the site # once it is removed I get the #NO MATCH so I set the " " or "Site Closed" or "VACANT" as the value if False.

Hi @Jim Kiltie

I would add the Row ID auto number column and the Parent Row ID helper columns for this solution.😀

How to fix your formula

Then, the LastNum formula becomes like this;

You want to use the zero padding numbering system like 01, 02, 03 instead of 1,2,3. In that case, it would be easier to do the padding here.

Then, the IDNum formula becomes like this.

Using ANCESTORS, you can cope with more than three level cases, like Parent, Child, and Grandchild.

Using IF(COUNT(ANCESTORS()) > 0, "-", "") removes the unnecessary delimiter at the top row.

You could use delimeter# instead of "-" if you use the Sheet Summary fields. (See at the bottom.)

More standard ways for auto-numbering

Please find more standard ways for auto-numbering on the right-hand side of the published demo sheet.

The difference between the fixed and dynamic is if you move the rows in a children group, the fixed one keeps the same Last Num or Self in my demo. In contrast, the dynamic one changes the Last Num depending on the relative position of the row in the same children group.

The formula for the "Self Dynamic" column is the same as for "LastNum." The "Self Fixed" uses the RANKEQ function instead of the MATCH function to get the absolute position of the row among the same children group.

You can specify the Prefix and delimiter in the Sheet Summary and call in the sheet cell formula as [filed name]#, delimiter#, for example.

By using the Sheet Summary, you can change those values dynamically.

@Vivien Chong, if you've still got the level column then yes, we can amend the formula slightly to get round this.

If you call your "testing 1" type task level 0, then:

=IF(Level@row <= 1, [Ep Number]@row, PARENT([Ep No]@row))

Sample:

Obviously if your level number system is slightly different then you will need to amend the number in the formula but hopefully this gives you the gist of what to do - let me know if there are any other issues. 🙂

I would suggest creating a row report pulling from the sheet that is grouped by service type then use the summary feature within the report to get your totals which can then be used for your chart.

Saves from having to use formulas and separate sheets and whatnot.