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

  • Fabrication Team
    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 Albrecht
    Jason 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?

    Hope this helps and that you have a great day,

    Jason

  • Bassam Khalil
    Bassam 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]

    ☑️ 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 EXPERT CONSULTANT & PARTNER

    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 for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Fabrication Team
    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.



  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!