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
-
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.
-
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.
-
@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.
-
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!