I am using the following. It is extremely effective, as you build up a
record of all orders handled over time. You can see at a glance which ones
are outstanding. You can of course colour completed rows, to show that they
are complete. I move every year's data into a new worksheet, and just
continue with the serial numbers. At present I have in excess of 30 000
orders recorded. It makes life really simple, as you can find anything
within seconds, using <Ctrl><F>.
I have my headings in Row 3.
A: Serial Nr Formula: (In A4 you will insert a 1)
=IF(AND(A4="",B4=""),"",IF(B5="","",A4+1))
B: Date order issued; Formula: (In B4 you will insert your Starting date)
=IF(C5="","",C4). Every day, you enter that day's date into the first row
for that day.
C: Order number
D: Cost price
E: Supplier (Suppliers tend to autofill, as you have this long list above)
F: Customer(Customers do the same)
G: Customer order nr; Formula: =IF(F4="","",IF(OR(F4="Stock",F4="your co's
name"),"No Number",""))
H: Supplier invoice nr; Formula: =IF(E4="","",IF(OR(E4="Stock",E4="In
House"),"No Number",IF(LEFT(E4,6)="Cancel","Cancelled","")))
I: Supplier Inv Date; Formula: =IF(E4="","",IF(OR(E4="Stock",E4="In
House"),"No Date",IF(LEFT(E4,6)="Cancel","Cancelled","")))
J: Supplier Inv cost; Formula: =IF(E4="","",IF(OR(E4="Stock",E4="In
House"),D4,IF(LEFT(E4,6)="Cancel","Cancelled","")))
K: Control column, compares D and J, to indicate OK or WRONG, Formula :
=IF(OR(D4="",J4=""),"",IF(LEFT(E4,6)="Cancel","Cancelled",IF(D4=J4,"OK","WRONG")))
L: Our Invoice nr. Formula:
=IF(LEFT(E4,6)="Cancel","Cancelled",IF(AND(J4<>"",F4="Stock"),"no
number",IF(AND(J4<>"",F4="Acumen"),"no number","")))
M: Selling price. Formula:
=IF(LEFT(E4,6)="Cancel","Cancelled",IF(AND(J4<>"",OR(F4="Stock",F4="Acumen")),J4,""))
N: Commission due. Formula:
=IF(OR(M4="",J4=""),"",IF(LEFT(E4,6)="Cancel","Cancelled",IF(M4-J4<0,M4-J4,(M4-J4)*0.2)))
O: Date finalised. Formula:
=IF(LEFT(E4,6)="Cancel","Cancelled",IF(AND(J4<>"",OR(F4="Stock",F4="(your
company name")),B4,""))
You can of cause use conditional formatting to change cell colours. I just
never got around to it
