Auto-number across multiple sheets?

I have three sheets that auto number, assigning a sheet specific prefix, a counter, and a year suffix. So on sheet one I have "New-XXXX-2015", on sheet 2 I have, "Existing-XXXX-2015".

I would like for the counter (XXXX) to increase upon each entry to either sheet. So if the first entry was "Existing-0001-2015", the next entry, regardless of where it is entered, would be assigned the number 0002.

Is this possible?

What about using a running counter as a vaiable in the autonumber process?

"{Project_Type}-{Project_Counter}-{YYYY}"

I think that would require cross-sheet data availability, wehich I don't think is available.

Find more posts tagged with

Comments

Bobby Andres

This is not yet possible in Smartsheet. Each sheet will have their own individual auto-numbers.

One idea, you could combine all three sheets into a single sheet then use three reports as your separate views. That way, you can have the same auto-number list for all your items but still have your separate views. You could also use a filter in your sheet to show different views.

Jamison

Thanks for the reply. The problem is that the data for the different types of work differ, So combining the sheets would not work well - there would be lots of blanks where columns exist but no data required for that type of entry.

Dan Davis

I have a similar situation where I have multiple Programs with multiple Projects within each Program. The suggestion from Bobby is what I have done, I have put all projects on a Master Project List. The format of the assigned project number has two parts, the first part has the Program deisgnator (7 characters), the second part has the auto number. The auto number has 6 characters, the first two are the fiscal year designator (ie: 16), the following four characters are auto numbered. The Smartsheet created auto number is in the first column for reference, the project number is manually typed in the second column; the Program number and the auto number, (example: UT0031-160078). It may sound a bit busy, but it's really not, it has worked well for me. The real benefit is when my master sheet is sorted in some way that doesn't produce the latest number assigned on the bottom of the list, I can still add a new project to the list and have a reference to the next new number from the auto number column.

I also rely on reports to publish data and would recommend you consider this as well. My spreadsheet has a lot of data in it, which serves primarily as a depository for the data. Sharing of the data happens on reports where only the needed columns and rows can be shown.

KrisWalsh

Here is our workaround for "Cross-Sheet Unique ID". Hope it helps!

We have 6 groups in our PMO. Each project is a sheet. There are currently ~200 active projects (sheets).

Each group has an Auto Number column in their Project Plan template with a 3 letter prefix for their group. TAD-00001, EOP-00001, etc.

We do not create a bunch of sheets at the exact same time, so we use a formula to concatenate Row ID and Created (Date/Time) into one UID.

UID1 =$[Row ID]1 + "-" + $Created1

Row ID Created UID

ID-0001 06/15/17 2:45 PM ID-0001-06/15/17 2:45 PM

We are reducing the likelihood of duplicate UID's tremendously, because the only way you can get one is if one group creates multiple projects in the exact same minute.

Using formulas with ROW ID and Created Date should work for most use cases.

Example Sheets: https://app.smartsheet.com/b/publish?EQBCT=429947fe7eb54a04bba8553dddbc565b