Assist in Auto-Numbering

Pestomania

Hi,

I cannot get my auto-number to work properly. The [Type of Book] can equal TB, BB, RB, or USB. I have used a similar formula before but I cannot get this one to auto-number like the others have and I am usnure what I have done incorrectly.

Thank you for any help.

=IF(AND(COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Type of Book]:[Type of Book], =[Type of Book]@row) = 0, [Confirmed Book Number]@row = ""), LEFT([Type of Book]@row, FIND(" - ", [Type of Book]@row)) + COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Type of Book]:[Type of Book]. <[Type of Book]@row), IF([Confirmed Book Number]@row <> "", [Confirmed Book Number]@row, LEFT([Type of Book]@row, FIND(" - ", [Type of Book]@row)) + , COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Type of Book]:[Type of Book], =[Type of Book]@row)))

KPH

Hi @Pestomania

The formula will probably return an UNPARSABLE error because you have a period where a comma is expected and a comma where nothing is needed. I have tried to highlight the offending characters in bold here.

=IF(AND(COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Type of Book]:[Type of Book], =[Type of Book]@row) = 0, [Confirmed Book Number]@row = ""), LEFT([Type of Book]@row, FIND(" - ", [Type of Book]@row)) + COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Type of Book]:[Type of Book]. <[Type of Book]@row), IF([Confirmed Book Number]@row <> "", [Confirmed Book Number]@row, LEFT([Type of Book]@row, FIND(" - ", [Type of Book]@row)) + , COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Type of Book]:[Type of Book], =[Type of Book]@row)))

Here is the formula without those, I've used bold here to highlight a larger portion - the area around where I made the changes:

=IF(AND(COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Type of Book]:[Type of Book], =[Type of Book]@row) = 0, [Confirmed Book Number]@row = ""), LEFT([Type of Book]@row, FIND(" - ", [Type of Book]@row)) + COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Type of Book]:[Type of Book], <[Type of Book]@row), IF([Confirmed Book Number]@row <> "", [Confirmed Book Number]@row, LEFT([Type of Book]@row, FIND(" - ", [Type of Book]@row)) + COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Type of Book]:[Type of Book], =[Type of Book]@row)))

Hopefully that was the issue you were experiencing.

Pestomania

@KPH Do you ever just face palm? Thank you, this is what occurs when you are trying to modify an existing formula in the tiny box of Smartsheet!

thank you

KPH

😂

The more eyes, the better!

Glad we got you unstuck.