Auto number when box is checked
question on how to generate a number when a checkbox is checked. This seems simpler than other peoples asks, but I can't seem to make it work for me. screenshot is the area that needs to be addressed. I want the [PO #] (currently set to auto-number every row)to generate the next number ONLY when the [PO needed?] is checked. Is there a formula that can do this? or will I have to upgrade to Business plan to get the ability to have this checkbox change another cells value?
These values are entered by a form.
The formula listed is the closest I can get within my knowledge!
Thanks in advance!
Best Answer
-
Thank you all. Jason you helped me figure it out.
What worked for my application was simpler than I thought.
=IF([PO# needed]@row = 1, MAX(PO2:PO100) + 1, 0)
Since this is set up as a form, I only needed it to work in the top two rows. then it would automatically use the function as new rows are added through the form. If you needed to add this to prior entries, by dragging to lower rows it will change the range from PO2:PO100 to PO3:PO101, PO4:PO102 etc.
Answers
-
First up - my experience says the specific question asked is not possible, as the auto number generates regardless of the check box or not (as you've figured out)
We compromised and decided not to worry if there were numbers missing between each one. We used the checkbox to add a number and hyphen to the front (e.g. 1496 became 2-1496). At one stage we were thinking about making the front number relative to the company or product, but decided against it.
As another compromise, if sequential numbering is required, you may want to forget about the auto number generation and use a max function within the PO field. e.g.
=IF([PO# needed?]@row = 1, max[PO #] + 1, 0) where [PO # ] = If([PO]@row ">" 0, [PO]@row, 0)
note, don't include the quotation marks around the "greater than" symbol. hope this helps?
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
Hi @Fabrication Team
Hope you are fine, i have a solution for this case but i need you to share some information with me. please call me :
My Email: Bassam.k@mobilproject.it
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
I hope you're well and safe!
I recently developed a similar solution for a client.
I'd be happy to share an example.
Please send me an email at andree@workbold.com, and I'll share it with you.
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thank you all. Jason you helped me figure it out.
What worked for my application was simpler than I thought.
=IF([PO# needed]@row = 1, MAX(PO2:PO100) + 1, 0)
Since this is set up as a form, I only needed it to work in the top two rows. then it would automatically use the function as new rows are added through the form. If you needed to add this to prior entries, by dragging to lower rows it will change the range from PO2:PO100 to PO3:PO101, PO4:PO102 etc.
-
SO HERE I AM AGAIN!
This sheet got messed up somehow, either by deleting the top row or??
I have used this exact same formula since my last post without issue. Recently its started to come back with a Circular Reference. This only happens on the SECOND time the form is used if the formula is not drag and dropped to the top cell.
to try and simplify what I'm doing and for anyone to help explain:
Basically this is all I need solved. Seems ridiculously simple but doesn't work. the formula cell references the prior cell. The formula will transfer correctly if pulled from bottom up or top down within the sheet, but never from the Form that created the top line.
I thought that Smartsheet pulled theformula from the top 2 rows, but it seems that its only pulling the top 1 row when using the Form to enter data. Here it is after entering one row:
What am I missing?
Cant find a way to do what essentially is "@row-1"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!