Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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: |
#4
|
|||
|
|||
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") |
#5
|
|||
|
|||
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: |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
|
#8
|
|||
|
|||
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: |
#9
|
|||
|
|||
"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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I assign sequential numbers in an invoice? | New Users to Excel | |||
Invoice Numbers | Excel Discussion (Misc queries) | |||
Generating Invoice Numbers | Excel Worksheet Functions | |||
Invoice numbers? | Excel Worksheet Functions | |||
how do you change invoice numbers automaticly | New Users to Excel |