Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, pleaseVisit the Current Forums.

Feature request: Add Auto-Number as a type option to Primary Column

Bill Jenkins
Bill Jenkins ✭
edited 12/09/19 inArchived 2015 Posts

I believe that the point of Primary Column is to possibly have a unique identifier for each row, beyond other possibilities. With that in mind, it would be useful to have Auto-Number as a type option for that column. I have some sheets where I need unique, autogenerated numbers, and I wish I could use the Primary Column for this.

· Share on Facebook Share on Twitter

Comments

  • SoniaR
    SoniaR ✭

    Hi Bill -

    While the Primary Column can currently only be a Text/Number column, you could use a formula to put the Auto-Number value in to the Primary Column. The formula would look something like:

    =[Row ID]1

    You'll want to change the words between the brackets to exactly match your column headers, including capitalization.

    Also, as new rows are added to the sheet, Auto-Fill may automatically fill in the formula in the Primary Column (based on whether a pattern is detected or not). Here's more info on Auto-Fill:

    http://help.smartsheet.com/customer/portal/articles/1641473

    Best,

    Sonia

    · Share on Facebook Share on Twitter
  • Ladislav
    Ladislav ✭
    edited 08/11/16

    Hello, I have tried the solution that SoniaR is proposing but it does not work. I am getting CIRCULAR REFERRENCE error message. My primary column "name" is INDEX. How the formula should look like ?

    · Share on Facebook Share on Twitter
  • Kennedy Stomps
    Kennedy Stomps Employee

    Hi Ladislav-- What is the name of your auto-number column? This is the name that you will want to put into the formula:

    =[auto-number column name]1

    This will copy the number from the auto-number column into the primary column.

    · Share on Facebook Share on Twitter
  • Ladislav
    Ladislav ✭

    Kennedy, I have misunderstood instructions from SoniaR. Now it certainly works, after having an additonal (auto-number, hidden) column.

    Thank you very much indeed !

    Ladislav

    · Share on Facebook Share on Twitter
  • Melanie Patrick
    Melanie Patrick ✭

    Another vote for being able to have auto-number as a field type for the Primary Column. While the workaround above absolutely works, it seems silly to have to use 2 fields for a single purpose.

    · Share on Facebook Share on Twitter
  • Rebecca Parker1
    Rebecca Parker1 ✭✭
    edited 01/18/17

    Hi - I can't get the workaround to work. My auto-number column is called "RowNum", and when I use the formula "=[RowNum]1", every column ends up with the value of "1".

    I'm also voting for auto-number to be an option for the Primary Column. I have a sheet where we are tracking customer interactions, and I'd just like to auto number them and have that be the primary key. Trying now to make up some concatenated value based on the person, the date and time, or something, but not having any luck.

    Thanks!

    · Share on Facebook Share on Twitter
  • latsyrcg3
    latsyrcg3 ✭✭✭

    I used the formula as stated above; however my sheet is a task list with multiple people adding new rows. I can expect the users of my sheet to add that formula every time they create task. An autonumber primary column would be so helpful.

    · Share on Facebook Share on Twitter
  • kbobofchak
    kbobofchak ✭

    Just confirming another user that would like to see this feature added. For shared lists relying on the copy feature has already caused difficulties and mistakes. I'm using the workaround suggested above and it's ok but not ideal.

    · Share on Facebook Share on Twitter
This discussion has been closed.

Categories

Hi there,<\/p>

I think this should work:<\/p>

=IF(COUNTIF(Referencia:Referencia, Referencia@row) > 1, 1)<\/p>

Have a great day.<\/p>

Matt<\/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":[]},{"discussionID":107890,"type":"question","name":"Need Help with Formula to Roll Up and Average Schedule Health.","excerpt":"Need Help with Formula to Roll Up and Average Schedule Health. I have one that now updates the Schedule Health RYG based on the Start Date\/End Date and Task Status. Next, I am trying to roll up\/average those Individual Task schedule health values to an overall Phase schedule health value and then ultimately to the Overall…","snippet":"Need Help with Formula to Roll Up and Average Schedule Health. I have one that now updates the Schedule Health RYG based on the Start Date\/End Date and Task Status. Next, I am…","categoryID":322,"dateInserted":"2023-07-21T00:56:52+00:00","dateUpdated":null,"dateLastComment":"2023-07-23T11:39:03+00:00","insertUserID":163738,"insertUser":{"userID":163738,"name":"BArmendariz","url":"https:\/\/community.smartsheet.com\/profile\/BArmendariz","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtcJ5xYSxEGHhOSSWfKvut9ttlA26d6GuoryTax_h13HbQ=s96-c","dateLastActive":"2023-07-21T16:10:48+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163540,"lastUser":{"userID":163540,"name":"stevediaz","title":"mr","url":"https:\/\/community.smartsheet.com\/profile\/stevediaz","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtfxw60LesP__pWW-huKx7XxMBqFzxmYEVfP7e4_MbE-=s96-c","dateLastActive":"2023-07-23T11:39:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":49,"score":null,"hot":3380018755,"url":"https:\/\/community.smartsheet.com\/discussion\/107890\/need-help-with-formula-to-roll-up-and-average-schedule-health","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107890\/need-help-with-formula-to-roll-up-and-average-schedule-health","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107890,"commentID":386551,"name":"Re: Need Help with Formula to Roll Up and Average Schedule Health.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386551#Comment_386551","dateInserted":"2023-07-23T11:39:03+00:00","insertUserID":163540,"insertUser":{"userID":163540,"name":"stevediaz","title":"mr","url":"https:\/\/community.smartsheet.com\/profile\/stevediaz","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtfxw60LesP__pWW-huKx7XxMBqFzxmYEVfP7e4_MbE-=s96-c","dateLastActive":"2023-07-23T11:39:49+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-21T16:11:26+00:00","dateAnswered":"2023-07-21T12:51:20+00:00","acceptedAnswers":[{"commentID":386383,"body":"

@BArmendariz<\/strong><\/a><\/p>

You will need to write a nested IF function to deal with the levels of hierarchy in your RAG column.<\/p>

You will have 1 set of IF's to deal with the Child Rows (usually based on Date and % complete etc)<\/p>

Then another set of IF's to deal with Parent Rows (usually based on if there is a Red ball in the children then the parent is red, if there is a yellow one, then parent is yellow etc)<\/p>

If the Project level (row1) logic needs to be different then a set of IF's for that will also be required.<\/p>

For example:<\/p>

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), \"Blue\") = COUNT(CHILDREN()), \"Blue\", IF(COUNTIF(CHILDREN(), \"Green\") = COUNT(CHILDREN()), \"Green\", IF(COUNTIF(CHILDREN(), \"Red\") > 0, \"Red\", \"Yellow\"))), IF([% Complete]@row = 1, \"Green\", IF([End Date]@row < TODAY(), \"Red\", IF([% Complete]@row = 0, \"Blue\", \"Yellow\"))))<\/p>


<\/p>

The first half of this is dealing with Parent Rows:<\/p>

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), \"Blue\") = COUNT(CHILDREN()), \"Blue\", IF(COUNTIF(CHILDREN(), \"Green\") = COUNT(CHILDREN()), \"Green\", IF(COUNTIF(CHILDREN(), \"Red\") > 0, \"Red\", \"Yellow\")))<\/p>

The opening condition: =IF(COUNT(CHILDREN([Task Name]@row)) > 0, is asking if there are any children (i.e. is this a Parent row) then this particular formula is then saying if it is a parent row then, if all the children are Blue then the Parent is also Blue, if all the children are Green then the Parent is also Green, if ANY of the children are Red then the Parent is also Red, or for anything else the Parent is Yellow.<\/p>

The second half is child rows:<\/p>

, IF([% Complete]@row = 1, \"Green\", IF([End Date]@row < TODAY(), \"Red\", IF([% Complete]@row = 0, \"Blue\", \"Yellow\"))))<\/p>

If the row is NOT a parent row then if % Complete = 100% then Green, if the row is Overdue then Red, if the % Complete is still 0 then Blue otherwise it is set to Yellow.<\/p>

I hope this helps...<\/p>

Kind regards<\/p>

Debbie<\/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":107888,"type":"question","name":"Filter distinct values only into new column","excerpt":"Hello! I have a column that consists of rows with values, some that differ and some that repeat [(Cells Linked from TE Tracker)], I want the next column over \"Distinct\" to only give me the unique values of [(Cells Linked from TE Tracker)] From what I've seen, there is no direct function that can do this, but has anyone…","snippet":"Hello! I have a column that consists of rows with values, some that differ and some that repeat [(Cells Linked from TE Tracker)], I want the next column over \"Distinct\" to only…","categoryID":322,"dateInserted":"2023-07-20T22:29:01+00:00","dateUpdated":null,"dateLastComment":"2023-07-22T20:06:16+00:00","insertUserID":163466,"insertUser":{"userID":163466,"name":"sawuzie","url":"https:\/\/community.smartsheet.com\/profile\/sawuzie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-22T20:06:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":163466,"lastUser":{"userID":163466,"name":"sawuzie","url":"https:\/\/community.smartsheet.com\/profile\/sawuzie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-22T20:06:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":42,"score":null,"hot":3379950317,"url":"https:\/\/community.smartsheet.com\/discussion\/107888\/filter-distinct-values-only-into-new-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107888\/filter-distinct-values-only-into-new-column","format":"Rich","lastPost":{"discussionID":107888,"commentID":386542,"name":"Re: Filter distinct values only into new column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386542#Comment_386542","dateInserted":"2023-07-22T20:06:16+00:00","insertUserID":163466,"insertUser":{"userID":163466,"name":"sawuzie","url":"https:\/\/community.smartsheet.com\/profile\/sawuzie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-22T20:06:45+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-22T05:31:23+00:00","dateAnswered":"2023-07-21T02:06:24+00:00","acceptedAnswers":[{"commentID":386312,"body":"

Hi @sawuzie<\/a> <\/p>


<\/p>

There's no direct way to do this. However, you can add a checkbox helper column to identify duplicates. You can use the formula =IF(COUNTIF([Cells Linked from TE Tracker]:[Cells Linked from TE Tracker], [Cells Linked from TE Tracker]@row) > 1, 1, 0). This will add a check in the duplicates column for all values which appear more than once. You can then add the Distinct column with the formula =IF([Duplicate]@row=0, [Cells Linked from TE Tracker]@row). The Distinct column will only have the distinct values listed.<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&siteSectionID=0&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending Posts

  • identifying a duplicate entry

    你好,我寻找一个公式红旗the duplicates entry in a previous column. Above an image that what can im looking for, thanks Im put the red flag manually for show you.

  • Need Help with Formula to Roll Up and Average Schedule Health.

    Need Help with Formula to Roll Up and Average Schedule Health. I have one that now updates the Schedule Health RYG based on the Start Date/End Date and Task Status. Next, I am trying to roll up/average those Individual Task schedule health values to an overall Phase schedule health value and then ultimately to the Overall…

  • 过滤不同值只有新列

    Hello! I have a column that consists of rows with values, some that differ and some that repeat [(Cells Linked from TE Tracker)], I want the next column over "Distinct" to only give me the unique values of [(Cells Linked from TE Tracker)] From what I've seen, there is no direct function that can do this, but has anyone…