# IF Checked, Assign Auto Number

Options

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

Tags:

• ✭✭✭✭✭✭
Options

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

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

• Options

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.

• ✭✭✭✭✭✭
Options

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.

• Options

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.

• ✭✭✭✭✭✭
Options

@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.

• Options

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

Thanks,

Randi

• ✭✭✭✭✭✭
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!