Custom Auto-Number?

Paul Newcome · 12/21/21

@Raul Cabrera You have a space that needs to be removed immediately after the opening square bracket in the first COUNTIFS.

Raul Cabrera · 12/22/21

@Paul Newcome i fix the formula but now just appear the first letter and What i am trying to have is first letter + 0001 + year. The 0001 should be the starting number but counting for each new item.

=LEFT([What is it?]@row + "-" + COUNTIFS([What is it?]$1:[What is it?]@row, [What is it?]@row, Created$1:Created@row, YEAR(@cell) = YEAR(Created@row)) + "-" + RIGHT(YEAR(Created@row), 2))

Paul Newcome · 12/22/21

My apologies. You also need to move one of the closing parenthesis from the very end to after the LEFT function.

Raul Cabrera · 12/22/21

@Paul Newcome can you help me writing the formula? i did what you said but still no good result

Raul Cabrera · 12/22/21

@Paul Newcome thanks. I think i got it. There were 2 parenthesis missing in the formula, now is working

I leave it here in case somebody needs it as example

=LEFT([What is it?]@row) + "-" + (COUNTIFS([What is it?]$1:[What is it?]@row, [What is it?]@row, Created$1:Created@row, YEAR(@cell) = YEAR(Created@row)) + "-" + RIGHT(YEAR(Created@row), 2))

Raul Cabrera · 12/22/21

@Paul Newcome something that i notice now that i input the formula is that i can not convert to a column formula, i think it is for the absolute reference using "$". Then the formula it is working but not as we need cause we need to copy and paste to new rows manually. Any suggestion to fix the problem with absolute refence and then we can assign as column formula?

Thanks as usual

Paul Newcome · 12/23/21

You are correct that the absolute reference makes it invalid for a column formula, but Smartsheet does still have an auto-fill feature. As long as there are two rows above and/or below the new row, auto-fill should grab it without you having to copy/paste anything.

If you are still wanting a column formula based solution, let me know. We would just need to add in two more helper columns.

Custom Auto-Number?

Answers

Help Article Resources

Categories