Auto number when box is checked

07/27/21
Accepted

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

  • Accepted 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

  • Jason AlbrechtJason Albrecht ✭✭✭✭

    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?

  • Bassam KhalilBassam Khalil ✭✭✭✭✭

    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: [email protected]

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ 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"

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi @Fabrication Team

    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 [email protected], 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 PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Accepted 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.



Sign In or Register to comment.