IF Checked, Assign Auto Number

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

Answers

  • Paul Newcome
    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)

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

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

  • @Andrée Starå

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


    Thanks,

    Randi

  • Andrée Starå
    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!

    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!