How to change auto number formula when value changes in a column

Hey! It's my first time posting here, and I really hope someone can help me! ๐Ÿ˜„

I have a formula which I'd like to modify. I will explain what I have and then how I'd want to change it.

I have a formula that auto-numbers indented rows, and resets the count to 1 for every parent row, like this:

โ†’ RowId column that is of type # Auto number

โ†’ Helper column with the formula =MATCH(RowId@row, RowId:RowId, 0), which makes sure the number are counted correctly

โ†’ IsParent which checks if the row has children, with =COUNT(CHILDREN()) > 0

โ†’ finally, Nr column with formula =IF(IsParent@row, Helper@row, Helper@row - PARENT()), which subtracts the value of the parent from the helper column so that children will always start counting from 1.

The problem:

I have two more columns, Name and NameChange:

โ†’ Name is a multiple choice column that can change values between A and B (and possibly C,D etc)

โ†’ NameChange is a column that becomes 1 every time the value from Name changes, or more like when it is different from the value in the cell above it, with the formula =IFERROR(IF(INDEX(Name:Name, MATCH(RowId@row - 1, RowId:RowId, 0))<> Name@row, 1, 0), 1)

What I need is the formula to start counting from 1 every time the Name column changes, (so every time NameChange is 1) like in the picture shown above, where I manually wrote the values of the Nr column.

I don't know how to do this without introducing another parent row every time the Name changes, which is really not ideal. I also want to keep Nr being a column formula, and can create helper formulas if needed, but I don't want to do anything manually because the sheet changes a lot during the day.

If you have any ideas please answer them here and thanks a lot!! ๐Ÿ˜ƒ

Tags:

Best Answer

  • Julzie
    Julzie โœญ
    edited 02/06/25 Answer โœ“

    Hey!

    Thanks a lot for the answer! I had to modify the formula a bit as it wasn't exactly what I wanted, but it really helped me!

    I modified it like this for Nr: =IF(IsParent@row, Helper@row, Helper@row - PARENT() - COUNTIFS(Name:Name, <>Name@row, Helper:Helper, <=Helper@row, [Parent Row]:[Parent Row], =[Parent Row]@row))

    What I needed was the following: keep count of how many rows with the same Name were already indented under the same parent. I did it by subtracting the no. of rows that didn't fit the criteria.

    I can also remove the Name Change column, it's not used.

    Result:

    Thanks a lot again! ๐Ÿ˜

    Edit: posted this comment form the wrong account oops ๐Ÿ˜…

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion

    Hi @Julie Mary

    I changed your sheet a bit as I don't know how to indent without inputting some date to name. (I input P, which is grayed out with the conditional formatting.)

    I also added a [Parent Row] helper column, which shows the row number of the parent.

    https://app.smartsheet.com/b/publish?EQBCT=c49711eb16d94e1ea105d5664e5ddbc5

    Then, I used the SUMIFS function as follows;

    [Nr] =IF(NOT(isParent@row), SUMIFS(NameChange:NameChange, Row:Row, <=Row@row, [Parent Row]:[Parent Row], [Parent Row]@row))
    [NameChange] =IF(NOT(isParent@row), IF(INDEX(Name:Name, MATCH(Row@row - 1, Row:Row, 0)) <> Name@row, 1, 0))
    [Parent Row] =PARENT(Row@row)

  • Julzie
    Julzie โœญ
    edited 02/06/25 Answer โœ“

    Hey!

    Thanks a lot for the answer! I had to modify the formula a bit as it wasn't exactly what I wanted, but it really helped me!

    I modified it like this for Nr: =IF(IsParent@row, Helper@row, Helper@row - PARENT() - COUNTIFS(Name:Name, <>Name@row, Helper:Helper, <=Helper@row, [Parent Row]:[Parent Row], =[Parent Row]@row))

    What I needed was the following: keep count of how many rows with the same Name were already indented under the same parent. I did it by subtracting the no. of rows that didn't fit the criteria.

    I can also remove the Name Change column, it's not used.

    Result:

    Thanks a lot again! ๐Ÿ˜

    Edit: posted this comment form the wrong account oops ๐Ÿ˜…

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion

    Happy to help!๐Ÿ˜

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!