Autogenerate IDs built off of other IDs?
Hello! I'm trying to auto-generate IDs that build off of another ID. The formula I'm currently using is:
=[Initial ID]@row + "-R" + COUNTIF([Initial ID]:[Initial ID], [Initial ID]@row)
I would ideally have it set to:
=[Initial ID]@row + "-R" + COUNTIF([Initial ID]1:[Initial ID]@row, [Initial ID]@row)
But that's not allowed in Smartsheet at the moment. Is there any way to run this so the IDs get numbered in order of being added and stay that way, rather than changing as more are added?
This picture hopefully demonstrates my issue. I want them to count up and have old ones not update.
Thanks!
Answers
-
Paul Newcome ✭✭✭✭✭✭It is possible using the last formula in your post, but it would have to be dragfilled to populate the existing rows (make sure that 1 is locked in as an absolute reference), and then the auto-populate feature will pull the formula in as long as there are at least two rows above and/or below the new row.
To get it set as a column formula, you would need to insert a text/number column (called "Row" in this example) and use this:
=MATCH([Initial ID]@row, [Initial ID]:[Initial ID], 0)
Then adjust your COUTNIFS to include this helper column like so:
条件统计 (.............................., Row:Row, @cell<=[email protected])
10xViz
-
pzupke ✭✭
Thanks for the response, Paul!
I was able to get the first suggestion to work, my only concern is that all of these existing rows are actually just for testing. Ideally it would start over from scratch. I suppose I could put in two dummy rows and hide them for the official release.
I'm struggling to get the second suggestion to work. It is giving the same results as my first attempt with the formula:
=[Initial ID]@row + "-R" + COUNTIFS([Initial ID]:[Initial ID], [Initial ID]@row, Row:Row, @cell <=[email protected])
The "Row" formula is exactly as you typed it.
If you are able to explain that second method I'm very open to learning more, but I at the very least have one option now, so thank you very much!
-
Paul Newcome ✭✭✭✭✭✭
I thought your Initial ID was a system generated Auto-Number column. It appears I was mistaken. Since that is the case, insert an auto-number column (called "Auto" in this example) and adjust the Row column formula to match on the auto-number column.
10xViz
-
pzupke ✭✭
@Paul Newcomethat did the trick! Yeah, the "Initial ID" column is entered into the sheet via form, but it is based on an auto-generated number on another sheet that we use to link other data. Thank you so much!
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
Check out theFormula Handbook template!
=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = \"Canceled\"), \"Canceled\", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = \"Complete\", @cell = \"Canceled\", @cell = \"On Hold\")), \"Complete\", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = \"Not Started\"), \"Not Started\", IF(COUNTIFS(CHLDREN(), @cell = \"Upcoming\")> 0, \"Upcoming\", \"In Progress\"))))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108218,"type":"question","name":"Help with \"AND\" formula","excerpt":"I'm using this formula where I want to generate the average complexity score for any projects in the year 2019 where the product is ESP. However, when I use this formula I'm getting an \"Invalid Data type\". I've confirmed all references are correct. Anyone have ideas? =AVERAGEIF(AND({Complexity Year}, \"2019\", {Product},…","snippet":"I'm using this formula where I want to generate the average complexity score for any projects in the year 2019 where the product is ESP. However, when I use this formula I'm…","categoryID":322,"dateInserted":"2023-07-27T20:23:56+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T12:47:12+00:00","insertUserID":156865,"insertUser":{"userID":156865,"name":"Katie S.","url":"https:\/\/community.smartsheet.com\/profile\/Katie%20S.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xWDX8YmJLfc!pKNXqRG2rHs!yB3MdbY6fmJ","dateLastActive":"2023-07-28T12:44:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":156865,"lastUser":{"userID":156865,"name":"Katie S.","url":"https:\/\/community.smartsheet.com\/profile\/Katie%20S.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xWDX8YmJLfc!pKNXqRG2rHs!yB3MdbY6fmJ","dateLastActive":"2023-07-28T12:44:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":33,"score":null,"hot":3381039668,"url":"https:\/\/community.smartsheet.com\/discussion\/108218\/help-with-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108218\/help-with-and-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108218,"commentID":387766,"name":"Re: Help with \"AND\" formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387766#Comment_387766","dateInserted":"2023-07-28T12:47:12+00:00","insertUserID":156865,"insertUser":{"userID":156865,"name":"Katie S.","url":"https:\/\/community.smartsheet.com\/profile\/Katie%20S.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xWDX8YmJLfc!pKNXqRG2rHs!yB3MdbY6fmJ","dateLastActive":"2023-07-28T12:44:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T12:46:42+00:00","dateAnswered":"2023-07-27T22:53:31+00:00","acceptedAnswers":[{"commentID":387693,"body":"
AVERAGEIF and AND do not play well together. Try this one, it collects all values that meet your criteria and then averages them. I am assuming {Complexity Score New} is the range with the values you would like to average.<\/p>
=AVG(COLLECT({Complexity Score New}, {Complexity Year}, \"2019\", {Product}, \"ESP\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108226,"type":"question","name":"COUNTIF and OR or IF Formula","excerpt":"I'm looking to use COUNTIF to calculate if something is the right date and then if it has meets one of the following criteria (using references). My current formula looks like this: =COUNTIFS({Despatch Report Date}, =Date8, {Despatch Report Lot Status}, \"Q\", {Despatch Report WO Status}, <99 I basically want to count if its…","snippet":"I'm looking to use COUNTIF to calculate if something is the right date and then if it has meets one of the following criteria (using references). My current formula looks like…","categoryID":322,"dateInserted":"2023-07-27T21:30:27+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T22:39:47+00:00","insertUserID":146974,"insertUser":{"userID":146974,"name":"Jack Parry","url":"https:\/\/community.smartsheet.com\/profile\/Jack%20Parry","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-27T22:59:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":156297,"lastUser":{"userID":156297,"name":"Pat Canning","url":"https:\/\/community.smartsheet.com\/profile\/Pat%20Canning","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5XYNMD97JXRY\/nD28D8BFM2BGU.jpg","dateLastActive":"2023-07-27T23:12:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":33,"score":null,"hot":3380991614,"url":"https:\/\/community.smartsheet.com\/discussion\/108226\/countif-and-or-or-if-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108226\/countif-and-or-or-if-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108226,"commentID":387692,"name":"Re: COUNTIF and OR or IF Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387692#Comment_387692","dateInserted":"2023-07-27T22:39:47+00:00","insertUserID":156297,"insertUser":{"userID":156297,"name":"Pat Canning","url":"https:\/\/community.smartsheet.com\/profile\/Pat%20Canning","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5XYNMD97JXRY\/nD28D8BFM2BGU.jpg","dateLastActive":"2023-07-27T23:12:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-27T22:59:50+00:00","dateAnswered":"2023-07-27T22:39:47+00:00","acceptedAnswers":[{"commentID":387692,"body":"
Does this work?<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/2UZI5G6FRBIB\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
