Help with excel - code you check sequential numbering

mksysb

Create a macro that adds one the the cell above, then assign a hot key to it

fifeken

mark88man wrote: »

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

Won't work if the previous order was over multiple lines. Selecting the two previous when they're the same value will just copy that number down.

Another suggestion if a bar code scanner is connected would be to make up a label to be scanned to generate a new order. Human readable could say "New Order" while the barcode could contain the formula from above.

WaywardDriver

To expand on my earlier post:

enter =A1+1 in cell A2 or whatever row you're starting in and copy this down the column;
select column A and click Conditional Formatting - New Rule - Use a formula to determine which cells to format;
in the "Format values where this formula is true" box type =AND(A2<>A1,A2<>A1+1)
click the Format button and set the Colour to Red for example; click OK.

Then whenever multiple rows are needed, simply overtype the formula with the repeated ID number.
The first part of the AND formula A2<>A1 will deal with these repeated IDs and the second part will check the next unique ID is 1 more than the previous one.

Heedtheadvice

IMHO, automatic/prenumbered rows is a good way to go. The fewer manual entries the better to reduce errors.
For that you really needs to keep each order record to one row.


Can you explain why you need more than one. It could be that there is an easy work around to prevent that need.


Code is good to use in some circumstances but you need to correctly produce the code, consider cases where assumptions do not hold true (and cater for them!). e sure all users have macros enabled (and with them attendant increased risk of malware type intervention), make sure someone else in the organisation understands it/it's implications when you are not at work etc.


However of the solutions provided to date I would just go with the simple conditioning format.
You could have green for sequential, amber for fepeat order number and flashing red out of sequence. Simple effective and very visible.

DoaM

unforeseen wrote: »

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

Exactly my thoughts too.

that

unforeseen wrote: »

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

Another database vote.

That spreadsheet may become corrupt. That spreadsheet may already be corrupt and is just awaiting for you to reach a certain point before it dies, and rolling back to a previous version may not help. Yes, i have seen this a few times.

Cornucopia

Just to point out that the OP has already said that the choice of platform is out of their hands.

I suppose on that basis, maybe a Macro might give some options for validating more than just the order number, and also it could be more intelligent in terms of re-asserting the number sequence after a multi-row order.

DoaM

Even if retaining as a spreadsheet, I'd still suggest to have an Orders worksheet (single line per order) and then an OrderLines worksheet ... where multiple lines containing the Order Number can be allocated as necessary. That way the Orders worksheet rows can easily be made sequential.

Heedtheadvice

Exactly, DoaM !
That approach is very possible, effectively seting different worksheets of the workbook to contain the tables as in a database and then treating them as such.
However that could be a big change to the OP's work and, however sensible, might not go down too well.
That really is the reason I asked the question about multiples lines (why?).
If it was just a matter of fitting all the entry into the cell size it might just be as simple as text wrapping and auto row height to solve the problem .i.e. Keep all data on one row with then very simple checking.
If it is one order number with separate items on each row then that (pseudo) relational database approach would have been the best solution....and a new proper relational database is probably still the best solution but not acceptable!