Help with excel - code you check sequential numbering

JohnB47

Hi all.

In the organisation I work for, we use an Excel spreadsheet to enter order details.

In the first cell of each new row created, we enter the next sequential number. So if the first cell of the last row used has, say, 23645 in it we start the next row by entering 23646 in the first cell.

Occasionally this gives us a problem - it is easy to get a bit muddled and transpose numbers. So using the above example, we might wrongly enter 26346 and from then on the wrong sequence continues until someone spots the error.

So my question is, is there some code I could use that checks the last sequential number used, then, if the next sequential number is not entered in the new row, an error message comes up?

One complication - some orders need more than one row in the spreadsheet and so the first few cells of a row newly created are often copied and pasted into further new rows. So you might then have 2, 3, 4 or more rows all with the same 'first cell' value, all constituting one order.

So, if this can be done, the code must activate only when the new number is entered by hand - not when a copy/paste is done.

I know what you're thinking - why not use a unique number generator for the first cell value, or change to using a database instead. These solutions are out of my hands I'm afraid - it's not going to happen. But if I get some clever code I might just persuade the boss to introduce it.

Thanks for any suggestions.

SuperPikachu

Have you tried using VBA -



https://docs.microsoft.com/en-us/office/vba/library-reference/concepts/getting-started-with-vba-in-office

PasturesNew

You could use colouring....

e.g. cells A1 and A2.

PseudoCode: If A2 is not equal to (A1+1) then turn this red and bold.

Cornucopia

As above, I would use conditional formatting. Should take no more than a couple of minutes to set up.

WaywardDriver

Conditional formatting as suggested with condition as, for example,
AND(A100<>A99, A100<>A99+1) to allow for multiple rows with same ID.

Tom99

Why not make cell A2 = A1+1.
Could you not just create the next 100 numbers in your spread sheet ready to use?

unforeseen

One complication - some orders need more than one row in the spreadsheet and so the first few cells of a row newly created are often copied and pasted into further new rows. So you might then have 2, 3, 4 or more rows all with the same 'first cell' value, all constituting one order.

That sounds like you should be using a database, not a spreadsheet.

fifeken

Tom99 wrote: »

Why not make cell A2 = A1+1.
Could you not just create the next 100 numbers in your spread sheet ready to use?

Not possible from the OP:

One complication - some orders need more than one row in the spreadsheet and so the first few cells of a row newly created are often copied and pasted into further new rows. So you might then have 2, 3, 4 or more rows all with the same 'first cell' value, all constituting one order.

fifeken

Tom99 wrote: »

Why not make cell A2 = A1+1.
Could you not just create the next 100 numbers in your spread sheet ready to use?

On the other hand, OP could instruct users not to type in the next number but to type in the formula as you've described with Ax = A(x-1) + 1 when a new order is to be generated.

mark55man

Use drag and drop
If it’s for the same order click the order number and drag down using the bottom right corner and extend to as many rows as you need and should fill with the same number

If it’s for a one up then select the two previous cells and drag down for as many orders as you want and it will one up each cell in turn

Forgive me if My visual explanation is poor I’m a numbers guy but it should be obvious and easy to explain to your users

The conditional formatting ideas would work well as a double check

JohnB47

Thanks everyone. Lots to think about there.

Much appreciated.