invoice numbers
 

I have created an invoice/proposal template that I want to have the invoice
numbers to be generated automatically to reflect the first three or four
letters of the customers name (in caps), how many times I've done business
with this individual, the month,day, and year of when the invoice was
created, and how many pages in the proposal/invoice were created. An example
of "Mr. Jones" second time doing business with me, written on May 28th, 2005,
and having three parts to the proposal, would look like this: JON0205280503.
Any suggestions would be appreciated.

I assume you have other cells with the lookup information.for example
C1 Last name
C2 Num of jobs
C3 date
c4 pages
try
=Upper(left(C1,3)&text(c2,"00)&if(month(c3)<10,"0" ,"")&month(C3)&if(day(C3)<10,"0","")&day(C3)&if(mo d(year(c3),100)10,"0","")&mod(year(c3),100)&if(C4 <10,"0","")&c4
"Murphey's Law" wrote:

I have created an invoice/proposal template that I want to have the invoice
numbers to be generated automatically to reflect the first three or four
letters of the customers name (in caps), how many times I've done business
with this individual, the month,day, and year of when the invoice was
created, and how many pages in the proposal/invoice were created. An example
of "Mr. Jones" second time doing business with me, written on May 28th, 2005,
and having three parts to the proposal, would look like this: JON0205280503.
Any suggestions would be appreciated.

Nice. You can simplify the date part to
=TEXT(C3;"mmddyy")
and Pages to
=TEXT(C4;"00")

Best wishes Harald

"bj" skrev i melding
...
I assume you have other cells with the lookup information.for example
C1 Last name
C2 Num of jobs
C3 date
c4 pages
try

=Upper(left(C1,3)&text(c2,"00)&if(month(c3)<10,"0" ,"")&month(C3)&if(day(C3)<
10,"0","")&day(C3)&if(mod(year(c3),100)10,"0","") &mod(year(c3),100)&if(C4<1
0,"0","")&c4
"Murphey's Law" wrote:

Sorry, too fast, those are localized functions. Replace the semicolons with
commas.

Best wishes Harald

"Harald Staff" skrev i melding
...
Nice. You can simplify the date part to
=TEXT(C3;"mmddyy")
and Pages to
=TEXT(C4;"00")

Thanks Harald, I now have "Mr. Jones" proposal number like this: JON10531051.
Of course this new number reflects the date I have on my computer which is in
a cell =NOW().
TWO THINGS: First, I need a double digit for the "times I've done business
for Mr. Jones", (01, 02, 03, etc.), and second, a double digit for the number
of pages in this proposal. I tried the formula I got from BJ, but for what
ever reason, it didn't work 100%, but it got me in the right direction!

Here is my formula so far: =UPPER(MID(A4,5,3))&L12&TEXT(I4,"mmddyy")&L15.
I'm almost finished, and Boy is it exciting! You have my sincere appreciation!

"Harald Staff" wrote:

Nice. You can simplify the date part to
=TEXT(C3;"mmddyy")
and Pages to
=TEXT(C4;"00")

Best wishes Harald

"bj" skrev i melding
...
I assume you have other cells with the lookup information.for example
C1 Last name
C2 Num of jobs
C3 date
c4 pages
try

=Upper(left(C1,3)&text(c2,"00)&if(month(c3)<10,"0" ,"")&month(C3)&if(day(C3)<
10,"0","")&day(C3)&if(mod(year(c3),100)10,"0","") &mod(year(c3),100)&if(C4<1
0,"0","")&c4
"Murphey's Law" wrote:

Hey BJ,
Although your formula didn't work 100%, I thank you for your help. Your
formula got me on the right track, and I'm grateful you were there. Thank you.
Please see the note I sent to "Harald".

"bj" wrote:

I assume you have other cells with the lookup information.for example
C1 Last name
C2 Num of jobs
C3 date
c4 pages
try
=Upper(left(C1,3)&text(c2,"00)&if(month(c3)<10,"0" ,"")&month(C3)&if(day(C3)<10,"0","")&day(C3)&if(mo d(year(c3),100)10,"0","")&mod(year(c3),100)&if(C4 <10,"0","")&c4
"Murphey's Law" wrote:

I have created an invoice/proposal template that I want to have the invoice
numbers to be generated automatically to reflect the first three or four
letters of the customers name (in caps), how many times I've done business
with this individual, the month,day, and year of when the invoice was
created, and how many pages in the proposal/invoice were created. An example
of "Mr. Jones" second time doing business with me, written on May 28th, 2005,
and having three parts to the proposal, would look like this: JON0205280503.
Any suggestions would be appreciated.

"Murphey's Law" <Murphey's skrev i melding
...
TWO THINGS: First, I need a double digit for the "times I've done business
for Mr. Jones", (01, 02, 03, etc.), and second, a double digit for the
number
of pages in this proposal. I tried the formula I got from BJ, but for what
ever reason, it didn't work 100%, but it got me in the right direction!

Those should both work like the Pages formula I provided
=TEXT(C2,"00")
=TEXT(C4,"00")
note that bj's suggestion has a small typo, no closing quote.

I'm almost finished, and Boy is it exciting!

Ain't that the truth.
Best wishes Harald

Try
=UPPER(MID(A4,5,3))&text(L12,"00")&TEXT(I4,"mmddyy ")&text(L15,"00")

Using the Now() in I4 is dangerous because it changes each time anything is
calculated and the historical invoice numbers will change everyday.

I would recommend either making sure you copy and paste special values on
the date column each day or enter the date using <control ;

"Murphey's Law" wrote:

Thanks Harald, I now have "Mr. Jones" proposal number like this: JON10531051.
Of course this new number reflects the date I have on my computer which is in
a cell =NOW().
TWO THINGS: First, I need a double digit for the "times I've done business
for Mr. Jones", (01, 02, 03, etc.), and second, a double digit for the number
of pages in this proposal. I tried the formula I got from BJ, but for what
ever reason, it didn't work 100%, but it got me in the right direction!

Here is my formula so far: =UPPER(MID(A4,5,3))&L12&TEXT(I4,"mmddyy")&L15.
I'm almost finished, and Boy is it exciting! You have my sincere appreciation!

"Harald Staff" wrote:

Nice. You can simplify the date part to
=TEXT(C3;"mmddyy")
and Pages to
=TEXT(C4;"00")

Best wishes Harald

"bj" skrev i melding
...
I assume you have other cells with the lookup information.for example
C1 Last name
C2 Num of jobs
C3 date
c4 pages
try

=Upper(left(C1,3)&text(c2,"00)&if(month(c3)<10,"0" ,"")&month(C3)&if(day(C3)<
10,"0","")&day(C3)&if(mod(year(c3),100)10,"0","") &mod(year(c3),100)&if(C4<1
0,"0","")&c4
"Murphey's Law" wrote:

"bj" wrote:

I assume you have other cells with the lookup information.for example
C1 Last name
C2 Num of jobs
C3 date
c4 pages
try
=Upper(left(C1,3)&text(c2,"00)&if(month(c3)<10,"0" ,"")&month(C3)&if(day(C3)<10,"0","")&day(C3)&if(mo d(year(c3),100)10,"0","")&mod(year(c3),100)&if(C4 <10,"0","")&c4
"Murphey's Law" wrote:

I have created an invoice/proposal template that I want to have the invoice
numbers to be generated automatically to reflect the first three or four
letters of the customers name (in caps), how many times I've done business
with this individual, the month,day, and year of when the invoice was
created, and how many pages in the proposal/invoice were created. An example
of "Mr. Jones" second time doing business with me, written on May 28th, 2005,
and having three parts to the proposal, would look like this: JON0205280503.
Any suggestions would be appreciated.