Auto Numbering Question

Diana36

Hoping someone can help me with a variation of this.

https://community.smartsheet.com/discussion/108362/auto-number-based-on-criteria

I have categories that are grouped by module that I would like to number based on just the category and date added. As they are grouped by module, they do not necessarily fall chronologically on the sheet but would like to number them that way so it shows in order on the report. For example, in row 14 I have a gap item that is dated 3/10 and on row 24 i have a gap item that is dated 2/13 but i need the row numbering to go in chronological order, not row order. I put the numbers in manually for now but would like to automate as more people get access to the sheet.

AKnight

Hi @Diana36 !

Just for clarification, your autonumbering will be based around Date Added and Category correct? You don't need the numbering to be for rows like 20 and 21 which have neither criteria? If so this formula should work for you assuming you create a new auto-numbering column type called "Row ID" (you can hide this column once it's created):

=IF(AND([Category]@row <> "", [Date Added]@row <> ""), COUNTIFS([Date Added]:[Date Added], @cell <= [Row ID]@row, [Category]:[Category], @cell = [Category]@row, [Date Added]:[Date Added], @cell = [Date Added]@row), "")

Let me know if that works for you!

Edit I noticed that those rows 20 and 21 cause a reset in the numbering. I missed that criteria piece so the formula above won't account for that. One moment while I work on something to fix that!

Diana36

Hi Ashley,

Thank you for responding. When I tried, I just got all 0.

AKnight

I'm so sorry I messed up changing the column names, try this:

=IF(AND([Category]@row <> "", [Date Added]@row <> ""), COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Category]:[Category], @cell = [Category]@row, [Date Added]:[Date Added], @cell = [Date Added]@row), "")

Diana36

Hi Ashley,

I feel like we are so close lol. So I got numbers this time but they seem to restart at 1 each date. I just want them to count chronologically. See below # column is what they should be (that I did manually).

AKnight

@Diana36 , for clarification, you don't want to have the numbers reset for date added, do you want them to reset when they are in a new module? Then count based on category? Or just count based on category?

Diana36

Hi Ashley,

I don't want them to reset for module or date added. I want them to continue in date order by category, regardless of module.

AKnight

@Diana36 Gotcha! This should work:

=IF(AND([Category]@row <> "", [Date Added]@row <> ""), COUNTIFS([Date Added]:[Date Added], @cell <= Date Added]@row, [Category]:[Category], @cell = [Category]@row), "")

And you shouldn't need the Row ID column anymore. This will also change the numbering if a date changes to be before the other dates that were previously recorded.

Let me know if that works

Diana36

I am getting a syntax error when trying to put in column formula and #UNPARSEABLE when in as a cell formula.

AKnight

Missing a bracket when making edits

=IF(AND([Category]@row <> "", [Date Added]@row <> ""), COUNTIFS([Date Added]:[Date Added], @cell <= [Date Added]@row, [Category]:[Category], @cell = [Category]@row), "")

Diana36

😥 not quite.

AKnight

Is there anything else that can differentiate numbers with the same category and date added? Maybe the time they were created (created date column type)? If there are no differentiations, then any auto-numbering system will mark them with the same number

Diana36

the issue is that a lot of them were added at the same time. it would probably work moving forward but not for the items that are already in there.

I guess I could just start with the formula for new entries.

AKnight

You mentioned that this is for report ordering, have you tried using the sort function in the report toolbar to order the rows in a way that you want?

Diana36

it doesn't work correctly because they are child rows attached to specific modules/meetings. I can sort on the reports but the numbering doesn't match up because of where they are on the sheet.

AKnight

Maybe adding some additional helper columns like Level:

=COUNT(ANCESTORS()) - this allows you to see how many times a row is nested in a child row.

Or Hierarchy:

=JOIN(ANCESTORS([Primary Column]@row), "-") - this allows you to see the name of the parent row(s)

You can generate a report that uses this source sheet, whatever columns, then filter level to be 2, sort by date added, and group by category. You can use hierarchy to see what module or meeting the item is from.