Auto numbering based on criteria and auto-reset
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!
Best Answer
-
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
Answers
-
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.
-
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?
-
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
-
Hope the above works!
-
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.
-
@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.
-
@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))
-
@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), ""))
-
Thanks so much @Lucas Rayala , that worked perfectly!!
-
@LM86 - fantastic -- have a great holiday :)
-
@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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 134 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!