By:

Kelly Anthony

- May 14, 2013

Posted in: 

With the latest Smartsheet update, we added a new column type that gives you the ability to automatically keep track of certain data points like a “modified by” date or “created by” date – also known as System Columns.  

System Columns are really handy for instantaneously keeping track of certain data points. They immediately add certain types of data into your rows, tracking information like the date a new row was added, or the name of the person that last updated a row.  

This week, we want to give you some tips and tricks to customize one of these new System Columns – the Auto-Number Column, to fit your processes and streamline your collaboration.  

Automagic!  Using Auto-Number System Columns

Automatically adding a unique identifier to a row is helpful in so many different scenarios: requests for help, customer orders, maintenance updates... the list goes on.

You can use the Auto-Number column to assign a simple value (e.g. 1, 2, 3) to new row submissions. If you’d like to make this column a bit more powerful and specific to your processes, change the Display Format criteria to create a combination of numbers and letters that provide context for your unique process.

Here are some examples of how you could use the Auto-Number Column to help streamline your processes:

  • Track Submissions by Quarter Looking to break sheet submissions down by quarter? An Auto-Number Column is perfect for this. At the beginning of the quarter, insert a Prefix value of “Q1-” in the column Display Format.  With this formatting updated, when new items are entered to your sheet, the Row ID will automatically include “Q1-” in front of a unique number.  When Q2 rolls around, all you need to do is update the Prefix from “Q1-” to “Q2-”.  

The unique numbers will continue, with the new quarter added as a prefix.  If you would like a new starting number at the beginning of each quarter, all you need to do is enter a Starting Number of “1” to the Starting Number field in the formatting form.

  • Track Submissions by Date Another handy prefix for Auto-Number Columns: add a date prefix to the Auto-Number Column to combine an automated unique ID with the date.  In the prefix field, add “{MM}/{DD}/{YY} -”.  This specifies to append the date in MM/DD/YY format to the prefix before a unique ID number.

There are four other types of system-generated Columns to choose from, in addition to Auto-Number:

  • Created (Date) This System Column is super handy for keeping track of the exact date and time that a row was created – GREAT to use with web forms!
  • Created By Use this System Column to keep track of the email address of any collaborator who created a row.
  • Modified (Date) Use this System Column to keep track of the last time a change was made to each row. 
  • Modified By This System Column will automatically keep track of the email address for the last person who updated data in a row.

NOTE: For both ‘Modified (Date)’ and ‘Modified By’ columns, Discussion updates, new attachments, and formatting changes (like a different font color) are not logged in this column.

A few other handy things to know about the new System Columns in Smartsheet:

  • Each type of System Column can only be used once in each sheet.
  • The cells in System Columns start out empty, and will generate value as you and others input data and save your sheet.
  • The values in System Columns are display-only.  You can adjust the formatting options for System Columns to adjust the appearance of these values.
  • If you change an existing column in your sheet to a Modified Date/By or Created Date/By system column, you will overwrite any existing cell data you have in those columns.

What are your ideas for using System Columns to save time?  Let us know.

Til next week, dig in and explore!

- Support Team

Comments

A time stamp system column would be great. It would allow for so many more uses of the smart sheet.

The Created by column always displays as "web-form@smartsheet.com" when a row is generated by a form submission. It seems that this field should be able to automatically pass in the user name when a collaborator of the sheet fills out the form. This is a major disadvantage compared to Google drive forms and sheets which collects the user name (gmail) automatically.

I've been looking everywhere for how to find the last modified date for a specific cell. I would like to find out when a checkbox value is changed to 1 so I can automatically know when it was completed. Any suggestions?

The Created and Modified date formatted cells appear in US date format (mm/dd/yy) no matter what the personal settings are. Is this a bug?

Hi Simon, Yes, this was a bug – confirmed and fixed on October 8th. Please let us know if you're still seeing the issue, happy to help! Thanks, Kelly

I am part of an international team that is using SmartSheet for project management. We have come up against the usual issue in such an environment of whether to interpret, for example, 6/7/14 as June 07, 2014 or July 06, 2014. For SmartSheet to be an effective tool in the international setting, having dates formatted with the month in alpha-characters, for example “04-Sep-14”, is essential. This is needed in the Column Properties rather than the Personal Settings. I understand that individual users can change their personal settings according to their preferred language and that this affects the date display. However, in collaborative on-line meetings where the SmartSheet is presented by a user in one country but seen by participants in various other countries, having dates formatted with the month in alpha-characters is critical to avoid international misunderstandings.

Hi Daniel, Thanks for your feedback – I've shared your request for different date formats with our product team. In the meantime, you can insert a text/number column and then use the month formula (info on that formula here: http://help.smartsheet.com/customer/portal/articles/775363-using-formulas) to find the month. Please feel free to email us at support@smartsheet.com if you need some extra help getting this formula to work in your project sheets. Thanks! Kelly

Can an autonumber field be treated as a number in a formula?

Hi Tomas, You can use the LEFT or RIGHT and VALUE functions to make the numbers in the autonumber column a number. Would look something like: =VALUE(LEFT([Row ID]1, 4)) If you have any other questions, feel free to email us at support@smartsheet.com for more help. Thanks! Kelly

Post new comment