Best Of

@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.

@Paul Newcome I am getting an incorrect argument set error. If I'm understanding it right, the first data reference is the column I'm pulling from which is the column that 'cars test' is in. The second data point is the column in my raw data spreadsheet, then contains is referencing the exact row that says 'cars test'.

I think you might be able to leverage an Auto-Number column (which would be hidden) that would work with a formula column to produce your result.

So, for example, you have an Auto-Number column, and it just consecutively numbers every row, regardless of transaction type, using whatever set-up you prefer. But, that column stays hidden. (It's shown in the screen cap below for reference, but it does not need to be visible.)

You create a PO Request column using a checklist column type and insert the following column-level formula:

=IF(OR([Payment Type]@row = "Payment Type A", [Payment Type]@row = "Payment Type B", [Payment Type]@row = "Payment Type C"), 1, 0)

("Payment Type A," "Payment Type B," and "Payment Type C" are just placeholders for the payment types that you DO want to produce a PO number - you may have more or less than 3 types - edit as needed.)

Then, in your PO Number column (which WILL be visible), create the following column-level formula:

=IF([PO Request]@row = 1, [Auto Number]@row, "n/a")

(NOTE: you can also just leave the rows that don't need a PO Number blank by replacing "n/a" with "" in your formula.)

Note that the one disadvantage here is that your PO Numbers will have gaps because some will not be used.

Hope this is helpful! Please feel free to tag me in a follow-up post if not, and I'm glad to help. ☺️

So glad to hear it worked for you!

Yes, as long as the cells don't have "Accepted" as the only word, you can have other columns between your ranges as well.