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!! 😃

Find more posts tagged with

Formulas

Accepted answers

Julzie

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 😅

All comments

jmyzk_cloudsmart_jp

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

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

Happy to help!😁

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.