keeping a running list
 

I have a workbook which contains blank purchase orders as well as
blank sales orders. I have to assign numbers to each purchase order
as well as to each sales order. The numbering format is (example):
25102250806..... here is how it works: first 3 numbers (251) are the
customer number, next 6 numbers (022508) is the date which the order
was taken, last 2 numbers (06) is the number of the purchase / sales
order I have received from that customer in that day. Pretty simple
really. Is there a way to, using this format, have excel
automatically generate the first 9 numbers so my employees only have
to type in the last 2, depending on how many orders from a particular
customer they get that day? Also, is there a way to create a macro
which will copy the numbers used for that day to either a document or
a different spreadsheet so we can check how many have been assigned?
Thank you in advance.
Daryl

keeping a running list
 

One way you can do this is as follows:

Col. A contains 3 digit customer number, Col. B is the date, Col. C is
the order number, Col. D is a hidden column used for a lookup, and
Col. E is your order number.

In col A, just enter the 3 digit customer number
Col. B contains the following formula =TEXT(TODAY(),"MMDDYYYY") to put
today's date in the DDYYMMMM format per your example above
Col. C is the order number containing the following formula:
=TEXT(COUNTIF($D$2:$D2,A2&B2),"000")
Col. D is a lookup value used in col. C containing this formula:
=A2&B2
Col. E is your new order #: =A2&B2&C2

This will increment the order number for each order placed in the same
day by the same customer.

Hope this helps.
Tim


On Feb 25, 12:52 pm, wrote:
I have a workbook which contains blank purchase orders as well as
blank sales orders. I have to assign numbers to each purchase order
as well as to each sales order. The numbering format is (example):
25102250806..... here is how it works: first 3 numbers (251) are the
customer number, next 6 numbers (022508) is the date which the order
was taken, last 2 numbers (06) is the number of the purchase / sales
order I have received from that customer in that day. Pretty simple
really. Is there a way to, using this format, have excel
automatically generate the first 9 numbers so my employees only have
to type in the last 2, depending on how many orders from a particular
customer they get that day? Also, is there a way to create a macro
which will copy the numbers used for that day to either a document or
a different spreadsheet so we can check how many have been assigned?
Thank you in advance.
Daryl

keeping a running list
 

On Feb 25, 1:49*pm, Tim879 wrote:
One way you can do this is as follows:

Col. A contains 3 digit customer number, Col. B is the date, Col. C is
the order number, Col. D is a hidden column used for a lookup, and
Col. E is your order number.

In col A, just enter the 3 digit customer number
Col. B contains the following formula =TEXT(TODAY(),"MMDDYYYY") to put
today's date in the DDYYMMMM format per your example above
Col. C is the order number containing the following formula:
=TEXT(COUNTIF($D$2:$D2,A2&B2),"000")
Col. D is a lookup value used in col. C containing this formula:
=A2&B2
Col. E is your new order #: =A2&B2&C2

This will increment the order number for each order placed in the same
day by the same customer.

Hope this helps.
Tim

On Feb 25, 12:52 pm, wrote:



I have a workbook which contains blank purchase orders as well as
blank sales orders. *I have to assign numbers to each purchase order
as well as to each sales order. *The numbering format is (example):
25102250806..... here is how it works: *first 3 numbers (251) are the
customer number, next 6 numbers (022508) is the date which the order
was taken, last 2 numbers (06) is the number of the purchase / sales
order I have received from that customer in that day. *Pretty simple
really. *Is there a way to, using this format, have excel
automatically generate the first 9 numbers so my employees only have
to type in the last 2, depending on how many orders from a particular
customer they get that day? *Also, is there a way to create a macro
which will copy the numbers used for that day to either a document or
a different spreadsheet so we can check how many have been assigned?
Thank you in advance.
Daryl- Hide quoted text -

- Show quoted text -

Works perfectly!!! Thank you so much!!! I have a question though....
when the order # is assigned, will it remain constant, or will it
changed every time I open the record on a different date? I need the
order #'s to remain constant for the sake of lookup and entry into our
accounting system. So, once the order is saved, I need the order # to
never change. Will that be the case here, or is there some other
formula I need to be using?

keeping a running list
 

Won't that change all the dates to TODAY()? Doesn't the OP want to
distinguish orders from different days?
--
David Biddulph

"Tim879" wrote in message
...
One way you can do this is as follows:

Col. A contains 3 digit customer number, Col. B is the date, Col. C is
the order number, Col. D is a hidden column used for a lookup, and
Col. E is your order number.

In col A, just enter the 3 digit customer number
Col. B contains the following formula =TEXT(TODAY(),"MMDDYYYY") to put
today's date in the DDYYMMMM format per your example above
Col. C is the order number containing the following formula:
=TEXT(COUNTIF($D$2:$D2,A2&B2),"000")
Col. D is a lookup value used in col. C containing this formula:
=A2&B2
Col. E is your new order #: =A2&B2&C2

This will increment the order number for each order placed in the same
day by the same customer.

Hope this helps.
Tim


On Feb 25, 12:52 pm, wrote:
I have a workbook which contains blank purchase orders as well as
blank sales orders. I have to assign numbers to each purchase order
as well as to each sales order. The numbering format is (example):
25102250806..... here is how it works: first 3 numbers (251) are the
customer number, next 6 numbers (022508) is the date which the order
was taken, last 2 numbers (06) is the number of the purchase / sales
order I have received from that customer in that day. Pretty simple
really. Is there a way to, using this format, have excel
automatically generate the first 9 numbers so my employees only have
to type in the last 2, depending on how many orders from a particular
customer they get that day? Also, is there a way to create a macro
which will copy the numbers used for that day to either a document or
a different spreadsheet so we can check how many have been assigned?
Thank you in advance.
Daryl

keeping a running list
 

Your right. I missed that.

You could change the process as follows:
1) Use the formulas above and at the end of each day, Copy / Paste
values over the dates so the date is no longer a formula
2) Manually enter the date at the beginning of each day and then just
copy it down each time you add a new order.



On Feb 25, 3:46 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
Won't that change all the dates to TODAY()? Doesn't the OP want to
distinguish orders from different days?
--
David Biddulph

"Tim879" wrote in message

...

One way you can do this is as follows:

Col. A contains 3 digit customer number, Col. B is the date, Col. C is
the order number, Col. D is a hidden column used for a lookup, and
Col. E is your order number.

In col A, just enter the 3 digit customer number
Col. B contains the following formula =TEXT(TODAY(),"MMDDYYYY") to put
today's date in the DDYYMMMM format per your example above
Col. C is the order number containing the following formula:
=TEXT(COUNTIF($D$2:$D2,A2&B2),"000")
Col. D is a lookup value used in col. C containing this formula:
=A2&B2
Col. E is your new order #: =A2&B2&C2

This will increment the order number for each order placed in the same
day by the same customer.

Hope this helps.
Tim

On Feb 25, 12:52 pm, wrote:
I have a workbook which contains blank purchase orders as well as
blank sales orders. I have to assign numbers to each purchase order
as well as to each sales order. The numbering format is (example):
25102250806..... here is how it works: first 3 numbers (251) are the
customer number, next 6 numbers (022508) is the date which the order
was taken, last 2 numbers (06) is the number of the purchase / sales
order I have received from that customer in that day. Pretty simple
really. Is there a way to, using this format, have excel
automatically generate the first 9 numbers so my employees only have
to type in the last 2, depending on how many orders from a particular
customer they get that day? Also, is there a way to create a macro
which will copy the numbers used for that day to either a document or
a different spreadsheet so we can check how many have been assigned?
Thank you in advance.
Daryl

keeping a running list
 

Are you willing and able to use VBA?

Some event code could adda static date to the customer number when the workbook
is opened or when an PO is started by entering data in some cell.

Customer number in A1

Date cell is B1

The concatenated 9 digit number/date ends up in C1 or in an inputbox where user
types a couple more numbers and your 11-digit number is generated into C1

If interested, how would you like to proceed?

What cells would you want to be used for the above event code and what cell is
most likely to be filled when you start a PO?


Gord Dibben MS Excel MVP


On Mon, 25 Feb 2008 09:52:02 -0800 (PST), wrote:

I have a workbook which contains blank purchase orders as well as
blank sales orders. I have to assign numbers to each purchase order
as well as to each sales order. The numbering format is (example):
25102250806..... here is how it works: first 3 numbers (251) are the
customer number, next 6 numbers (022508) is the date which the order
was taken, last 2 numbers (06) is the number of the purchase / sales
order I have received from that customer in that day. Pretty simple
really. Is there a way to, using this format, have excel
automatically generate the first 9 numbers so my employees only have
to type in the last 2, depending on how many orders from a particular
customer they get that day? Also, is there a way to create a macro
which will copy the numbers used for that day to either a document or
a different spreadsheet so we can check how many have been assigned?
Thank you in advance.
Daryl