Auto number when box is checked

Fabrication Team

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!

Fabrication Team

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.

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?

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: Bassam.k@mobilproject.it

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 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!

Fabrication Team

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.

Fabrication Team

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"