Start Row ID part way through a new SS

Si Spence

Dear SS Guru's

 

we have a smartsheet that has been imported from Excel.  and each row has a four digit 'DVS' code that i want to automate.

 

I can create an 'auto number/created date' column but because over time some rows in the excel doc were deleted or becuase numbers have been assigned out of order they do not run concurrently.

 

the problem is that even if i count back and start my auto number so that the next row created is correct all the previous DVS codes that have already be assigned to a project are incorrect.

 

I'm not sure that I'm making sense but if i could format a column to 'auto number' and if that then started from a certain row number then i could manually add in the previous data.

 

what do you think please?

Boomba

The "Auto-Number" option allows you to set the starting number. You could give yourself a buffer and start auto-numbering from a unique first digit. For example, if your highest DVS code previously had been 2800, you could set your sheet to auto-number from 4000. That way you could clearly tell the new codes from the old ones.

 

And, if that doesn't work, you can take the old data and put it into one sheet, and then start a new sheet for all new data "from this day forward". Eventually the old sheet can be retired, when all of those codes expire, and you will be left with the new one that has all the proper data in the proper formats. It's a little bit klunky when you have to use two sheets, but having clearly distinct DVS codes can help cue you which sheet a specific range of codes is in.

Si Spence

Hi Boomba,  i appreciate your kind help although working on two sheets isnt an option.  For what it is, and unless anybody has an alternative idea, then we will have to continue to apply manual coding.  Thank you.  Si.

Boomba

Hmm. Ok.

 

Re-reading your original post, it's the 3rd paragraph that confuses me.

 

So, are the DVS codes sequential? Do they follow a specific format? Are the numbers dictated to you, or can you use your own numbers?

 

It seems like auto-numbering could be the solution... but I may not be understanding the problem fully.

Si Spence

Hi Boomba (cool name by the way)

 

yes,  numbers are sequential although some fell out of sequence due to human error and some rows have been deleted over time. ideally need to start a new sequence from when we transferred the spreadsheet into SS and then just copy paste the others over the top of the earlier entries. 

 

Or or we could have two columns with the new column  auto creating the new DVS codes....although this will cause issues when searching by DVS. 

 

Id like to automate but perhaps not possible given our starting point. 

Boomba

Oh. Right. And you can't edit the contents of an Auto-Number column, so you can't hand-enter the oddball numbers from the old list of DVS codes. Got it.

 

Here's my idea:

 

First, create a DVS Code column.

 

Second, create an Auto-Number column, and Hide it.

 

Write an IF/THEN that tests IF the DVS Code column is EMPTY, THEN use the Auto-Number field from that row.

 

With this method, you can hand-enter the old DVS Codes directly into the DVS Codes column, and those rows will stay the way you entered them.

 

But new rows would have an auto-generated DVS code.

 

(Alternately, you could have THREE columns: DVS Code, Old DVS, and Auto-number. The IF/THEN would then be IF Old DVS > 1, THEN Old DVS, ELSE Auto-number. This would simply drop either the Old DVS code into the DVS Code field, or - if that was blank - put in the Auto-number for that row. Then you could hide both the Old DVS and Auto-number fields, and only see the DVS Code field.)

Si Spence

Sounds like a workable solution. I'll give hat a go. Thank you. 

J. Craig Williams

Did you try this: ?

 

1. Import the sheet. The Excel auto-number column should be a Text/Number column.

2. Verify all cells in the column have a value

3. Change column to Auto-Number with the expected format and starting number.

4. Save.

 

Next row should start with your desired Auto-Number. Existing cells won't be updated or erased.

 

Note that a 'number' like 0023 is really text for the Auto-Number column.

If it is all numbers but is padded, it is text, otherwise it is a number (useful to know if using the value in a formula)

 

Hope this helps.

 

Craig

Si Spence

Hi Craig,  this only works if you set up at teh time of creating the sheet, doenst seem to work retrospectively. Boombas' idea, was a fiddly workaround but we tried it and it seems to works okay so we seem set for now.

 

Thanks all for your help again,  Si.

J. Craig Williams

Si,

 

The limitation is that we can not import to an existing sheet (in the past, I have fixed this with the API)

 

There also isn't an easy way to merge two sheets that have overlapping row data.

I have a process for that too, but it is very manual - so often I need some data from each row for the new merged row - the computer can't make that decision yet.

 

You can also turn Auto-Number off / save / on / save - repeat as necessary to get your final resullts you want.

 

Hopefully others will benefit when running into the same problem.

Craig