IF Checked, Assign Auto Number

Randi Dean

Hi there!

I'm no excel or Smartsheet formula wiz but I'm hoping someone here can help me figure out a way to do the following:

I'd like an auto-populated number to appear in a row when the "Estimate" box is checked. There will be some rows where estimate is not checked and therefore I don't want to dedicate a row to a number that will make other out of sequence

I have columns Number, Estimate, Pk

[Number] [Estimate] [Project Name]

000001 X 20-TEST-JOBA

20-TEST-JOBB

000002 X 20-TEST-JOBC

Paul Newcome

Try putting something like this in row 1 and then drag-filling it down the remaining rows...

=COUNTIFS(Estimate$1:Estimate@row, 1)

Randi Dean

Thanks for the reply @Paul Newcome! That didn't quite work for my needs though.

When 'Estimate" is checked, I want Estimate No. to generate a unique sequential number (ie: 00001, 00002, 00003). I'd like for the sequential order to only apply to rows that are estimates. So if Row 1 is an Estimate Checked, It would have. And it would be REALLY great if that number pattern is uninterrupted by rows that are not checked. See my second photo of what I'd like...

Then, to add one more level of complexity to this... I don't want that Estimate No. to change if a row is deleted.

Paul Newcome

Ok. That makes sense.

To generate the sequential number...

=IF(Estimate@row = 1, IF(COUNTIFS(Estimate$1:Estimate@row, 1) < 10, "0000", IF(COUNTIFS(Estimate$1:Estimate@row, 1) < 100, "000", IF(COUNTIFS(Estimate$1:Estimate@row, 1) < 1000, "00", IF(COUNTIFS(Estimate$1:Estimate@row, 1) < 10000, "0")))) + COUNTIFS(Estimate$1:Estimate@row, 1))

To lock in that number, you would need to copy the row to another sheet, leverage the API, or use a 3rd party app such as Zapier. Depending on exactly how you set that portion up, there is still the possibility of having a duplicate if you are deleting rows, but there could be variables we could incorporate into the formula to account for that.

Randi Dean

This works so far!

I'm not sure I fully understand how to lock in the value though... Sorry, my knowledge on this is basic. I'd rather not have to involve copying to another sheet. I've used Zapier before but not exactly sure how to get one columns update to affect another columns data based on Zapier's dropdown options.

Paul Newcome

@Andrée Starå is MUCH more familiar with Zapier than I am . I only use it for one very specific purpose which is simply keeping a current TODAY() function on sheets that may not be activated daily, but I have seen it mentioned before where Zapier is a great option for locking in static data.

Randi Dean

@Andrée Starå

Hi Andrée, are you able to lend any insight to my challenge?

Thanks,

Randi

Andrée Starå

Hi @Randi Dean,

Sure!

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 help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!