Auto numbering based on criteria and auto-reset

LM86

Hello!

I'm trying to auto-number two columns (Cell Batch and Passage#) based on Project Code, Batch ID and Cell type.

• IF Project Code and Batch ID are different, increase Passage# by +1 for either HEK or CHO, if passage # is 33, then reset from 1.
• IF Passage# for HEK or CHO reach 33, then Batch ID +1
• Note: HEK and CHO Batch IDs and Passage#s are out of sync

In the example below, I manually put in the numbers for Cell Batch and Passage# but hope to have those automated. Rows are x4 for some ReqIDs due to some Indexing req'd for Start Dates.

Hopefully that context is helpful.

Thank you in advance!

Lucas Rayala

Hi @LM86, I had to solve a similar problem a couple of years ago, so hat-tip to @Leibel S for helping me over at this post:

Batching sequential rows that are "like groups" with a formula — Smartsheet Community

In your description, you don't actually provide any requirements for numbering "Cell Batch". However, I believe I have the Passage# correct. I've used a lot of helper columns to make this work. Some of these could be consolidated, but you can also just hide them all.

Create the following columns with the following column formulas (to create a column formula, just right click a working formula and select Convert to Column Formula):

UniqueGroupID

=[Project Code]@row + [Batch ID]@row + [Cell type]@row

Row ID

create this as an # Auto number column

ROW#

=MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

First Group

=IF([ROW#]@row = 1, 1, IF(INDEX(UniqueGroupID:UniqueGroupID, [ROW#]@row - 1) = UniqueGroupID@row, 0, 1))

PassageRef

=IF([Project Code]@row = "", "", COUNTIFS([Cell type]:[Cell type], [Cell type]@row, [ROW#]:[ROW#], <=[ROW#]@row, [First Group]:[First Group], 1))

Passage# (note this is your existing column)

=PassageRef@row - (ROUNDUP(PassageRef@row / 33, 0) - 1) * 33

Lucas Rayala

Hi @LM86 -- did you still need help with this? If so, you'll need at least one column that is auto-numbered. Quick question -- will the page be re-ordered at any time? Will anyone be sorting the columns, or will they be moved around? If so, what should happen? That will impact how you set this up.

LM86

Hi @Lucas Rayala

Yes! Still in need of help. Page will not be re-ordered/sorted. I have some automations set up that take new rows but they will be added to the bottom of the page. Re the autonumbering - are you suggesting to use the Smartsheet autonumbering system? Or a conditional formula for autonumbering?

Lucas Rayala

Hi @LM86, I had to solve a similar problem a couple of years ago, so hat-tip to @Leibel S for helping me over at this post:

Batching sequential rows that are "like groups" with a formula — Smartsheet Community

In your description, you don't actually provide any requirements for numbering "Cell Batch". However, I believe I have the Passage# correct. I've used a lot of helper columns to make this work. Some of these could be consolidated, but you can also just hide them all.

Create the following columns with the following column formulas (to create a column formula, just right click a working formula and select Convert to Column Formula):

UniqueGroupID

=[Project Code]@row + [Batch ID]@row + [Cell type]@row

Row ID

create this as an # Auto number column

ROW#

=MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

First Group

=IF([ROW#]@row = 1, 1, IF(INDEX(UniqueGroupID:UniqueGroupID, [ROW#]@row - 1) = UniqueGroupID@row, 0, 1))

PassageRef

=IF([Project Code]@row = "", "", COUNTIFS([Cell type]:[Cell type], [Cell type]@row, [ROW#]:[ROW#], <=[ROW#]@row, [First Group]:[First Group], 1))

Passage# (note this is your existing column)

=PassageRef@row - (ROUNDUP(PassageRef@row / 33, 0) - 1) * 33

Lucas Rayala

Hope the above works!

LM86

Thank you @Lucas Rayala !! Formulas worked perfectly! Just need to figure out the Batch #, it starts at x value (what we have currently recorded), say 55, and increments by +1 when passage# reaches 33 for HEK or CHO.

Lucas Rayala

@LM86 -- it sounds like the Batch number has an arbitrary start right now? If that's the case, I would enter that value in the Sheet Summary tab for reference within your sheet. I've included a link to an article on Sheet Summary info at the bottom. Name the field "Start" for now, and enter 55.

In the Cell Batch, I think you just need this formula then:

=Start# + ROUNDUP(PassageRef@row / 33, 0)

Note that the hashtag convention is used to reference fields in the Sheet Summary tab.

Sheet Summary | Smartsheet Learning Center

LM86

@Lucas Rayala That Sheet Summary is something I've never used before, thanks for that tip! I think I'm close...

I tried the following formula because I only want the Batch# to increase if Passage Number is =33 and the Batch# is tied to the Cell Type. So my sheet summary has two references now. (I'm sure there's a cleaner way to do this...) But this one is giving me an error:

=IF(AND([Cell Type]@row = "CHO", PassageRef@row < 34), [CHO Batch#]#, IF(AND([Cell Type]@row = "HEK", PassageRef@row < 34), [HEK Batch#]#), IF(AND([Cell Type]@row = "CHO", PassageRef@row = 33), [CHO Batch#]# + 1, [HEK Batch#]# + 1))

Lucas Rayala

@LM86 -- if you have a different starter batch number for the two cell types, you can modify the formula I provided to account for that:

=IF([Cell type]@row = "CHO", [CHO Batch#]# + ROUNDUP(PassageRef@row / 33, 0), IF([Cell type]@row = "HEK", [HEK Batch#]# + ROUNDUP(PassageRef@row / 33, 0), ""))

LM86

Thanks so much @Lucas Rayala , that worked perfectly!!

Lucas Rayala

@LM86 - fantastic -- have a great holiday :)

Lucas Rayala

@LM86 - a couple of things to know about this solution.

1.) When new items load, you may need to save and refresh for the order to populate correctly.

2.) The numbering is dynamic based on how the rows are physically placed on the screen. Reordering rows will reorder the numbering as well (this is done using the Row# column as a order reference, but it gets geeky so don't worry about it too much).

3.) Autonumbering like this starts to get resource intensive. You probably can't have more than 2,000 rows on this page (see the link at the bottom for a description why). If this number is an issue, you should look into archiving old data. There's other workarounds, but hopefully you won't need them.

Explanation of "Too Many Cells Referenced" Error - 25M Restriction — Smartsheet Community