invoice numbers

G

Guest

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.
 
G

Guest

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

Harald Staff

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

Best wishes Harald
 
H

Harald Staff

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

Best wishes Harald
 
G

Guest

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!
 
G

Guest

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".
 
H

Harald Staff

Murphey's Law said:
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
 
G

Guest

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 ;>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top