Start Row ID part way through a new SS

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.

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.

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.)

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,

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