create self-generating numbers with letters and numbers

[cxlough41]

i am trying to create a workbook which the first page is the customer
information page. this is done... now i need to use the first two letters
from each cell containing the first and last names... now i need to add the
current date in ddmmyy format NO PUNCTUATION...followed by a dash and 0000
which will increase by 1 each time the name cells are filled in. i would
also like to add auto "save as" with this generated number as the name of the
work book. every customer gets a new work book for each job ( 1 cust / 10
jobs = 10 work books )

this is what i am looking for;

customer name is Will I. Twirk
he calls for service on 01/06/05
after confirming his interest we create a new work book for the new customer
I open the work book template
I add customers last name
I add cust first name
when i press tab next... i want the number to generate and show up as
TWWI010605-0001

can this happen??? HELLLPPPP!!!!!

I don't want to push my luck but..... it would be really nice if this number
could be sent to the invoice page to create the same number but with an I
just be4 the dash.

and if i have not figured by then how to do the same for the estimate page
with an E instead of an I ...... what the heck can you show that too???
pleeeeease heeeeellllp.

i really need to be able to track by name so this seems like it would really
suit my needs

[Kleev]

I can't help you with the rest of your question, but a formula to create the
text you asked for, along with some test data I used is:
Twirk Will 1/6/2005 TWWI010605-0001
Twirk Will 1/6/2005 TWWI010605-0002
Kirk James 7-Jan KIJA010705-0003
White Vanna 3/1/2005 WHVA030105-0004
Of the Apes Tarzan 12/30/2005 OFTA123005-0005

UPPER(LEFT(A12,2))&UPPER(LEFT(B12,2))&TEXT(MONTH(C12),"00")&TEXT(DAY(C12),"00")&RIGHT(YEAR(C12),2)&"-"&TEXT(ROW()-11,"0000")

This is using US date style. I could not tell from your sample what date
style you are using (as 1/6 could be January 6 or June 1.)

The formula started on row 12 (as should be obvious) which is why I
subtracted 11 from row() (to begin on 1.) Modify to suit your needs.

"cxlough41" wrote:

> i am trying to create a workbook which the first page is the customer
> information page. this is done... now i need to use the first two letters
> from each cell containing the first and last names... now i need to add the
> current date in ddmmyy format NO PUNCTUATION...followed by a dash and 0000
> which will increase by 1 each time the name cells are filled in. i would
> also like to add auto "save as" with this generated number as the name of the
> work book. every customer gets a new work book for each job ( 1 cust / 10
> jobs = 10 work books )
>
> this is what i am looking for;
>
> customer name is Will I. Twirk
> he calls for service on 01/06/05
> after confirming his interest we create a new work book for the new customer
> I open the work book template
> I add customers last name
> I add cust first name
> when i press tab next... i want the number to generate and show up as
> TWWI010605-0001
>
> can this happen??? HELLLPPPP!!!!!
>
> I don't want to push my luck but..... it would be really nice if this number
> could be sent to the invoice page to create the same number but with an I
> just be4 the dash.
>
> and if i have not figured by then how to do the same for the estimate page
> with an E instead of an I ...... what the heck can you show that too???
> pleeeeease heeeeellllp.
>
> i really need to be able to track by name so this seems like it would really
> suit my needs

[pinmaster]

Instead of
TEXT(ROW()-11,"0000")
you could change it to
TEXT(ROW(1:1),"0000")
that way no matter where you start your formula it will always return a 1 in the first cell.

Regards
JG

[cxlough41]

this looks like it will do. with this formula will the -0000 always increase
by 1... that is what i wanted it to do so i know which job number the job
was. ie:0001 is the first job of the business - 0023 is the 23rd job -
0124...so on and so on.

I take it you said you counldn't answer rest of question ie: "I don't want
to push my luck but..... it would be really nice if this number
> > could be sent to the invoice page to create the same number but with an I
> > just be4 the dash.
> >
> > and if i have not figured by then how to do the same for the estimate page
> > with an E instead of an I ...... what the heck can you show that too???"

if so this is fine... i am glad i can do this much...lol

Tanks so much!


"Kleev" wrote:

> I can't help you with the rest of your question, but a formula to create the
> text you asked for, along with some test data I used is:
> Twirk Will 1/6/2005 TWWI010605-0001
> Twirk Will 1/6/2005 TWWI010605-0002
> Kirk James 7-Jan KIJA010705-0003
> White Vanna 3/1/2005 WHVA030105-0004
> Of the Apes Tarzan 12/30/2005 OFTA123005-0005
>
> UPPER(LEFT(A12,2))&UPPER(LEFT(B12,2))&TEXT(MONTH(C12),"00")&TEXT(DAY(C12),"00")&RIGHT(YEAR(C12),2)&"-"&TEXT(ROW()-11,"0000")
>
> This is using US date style. I could not tell from your sample what date
> style you are using (as 1/6 could be January 6 or June 1.)
>
> The formula started on row 12 (as should be obvious) which is why I
> subtracted 11 from row() (to begin on 1.) Modify to suit your needs.
>
> "cxlough41" wrote:
>
> > i am trying to create a workbook which the first page is the customer
> > information page. this is done... now i need to use the first two letters
> > from each cell containing the first and last names... now i need to add the
> > current date in ddmmyy format NO PUNCTUATION...followed by a dash and 0000
> > which will increase by 1 each time the name cells are filled in. i would
> > also like to add auto "save as" with this generated number as the name of the
> > work book. every customer gets a new work book for each job ( 1 cust / 10
> > jobs = 10 work books )
> >
> > this is what i am looking for;
> >
> > customer name is Will I. Twirk
> > he calls for service on 01/06/05
> > after confirming his interest we create a new work book for the new customer
> > I open the work book template
> > I add customers last name
> > I add cust first name
> > when i press tab next... i want the number to generate and show up as
> > TWWI010605-0001
> >
> > can this happen??? HELLLPPPP!!!!!
> >
> > I don't want to push my luck but..... it would be really nice if this number
> > could be sent to the invoice page to create the same number but with an I
> > just be4 the dash.
> >
> > and if i have not figured by then how to do the same for the estimate page
> > with an E instead of an I ...... what the heck can you show that too???
> > pleeeeease heeeeellllp.
> >
> > i really need to be able to track by name so this seems like it would really
> > suit my needs

[cxlough41]

I think i know what this small change does but I am not absolutely sure.
Could you send a reply to verify what this change will do.

Thank you so much!

"pinmaster" wrote:

>
> Instead of
> TEXT(ROW()-11,"0000")
> you could change it to
> TEXT(ROW(1:1),"0000")
> that way no matter where you start your formula it will always return a
> 1 in the first cell.
>
> Regards
> JG
>
>
> --
> pinmaster
> ------------------------------------------------------------------------
> pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
> View this thread: http://www.excelforum.com/showthread...hreadid=496864
>
>

[cxlough41]

this works AWSOME! thanks... but... i need to place a cell with the current
date when i first add customer. because this date is part of the customer
number the date can not be volitile... ie: if customer was created on 1/12/06
and name is Joe Shmoe the cust. no. will be SHJO011206-0001 but if volitile
date format i would get this when i open file on 1/18/06 : SHJO011806-0001
and this will change every time i open the file.

Q: how do i create an automatic current date to the date i first create
file with out updateing the current date at each open of file? wooo that was
a wierd sentence.

Again the prior help was so cool... it works! it works!

thanks again soo much!

"Kleev" wrote:

> I can't help you with the rest of your question, but a formula to create the
> text you asked for, along with some test data I used is:
> Twirk Will 1/6/2005 TWWI010605-0001
> Twirk Will 1/6/2005 TWWI010605-0002
> Kirk James 7-Jan KIJA010705-0003
> White Vanna 3/1/2005 WHVA030105-0004
> Of the Apes Tarzan 12/30/2005 OFTA123005-0005
>
> UPPER(LEFT(A12,2))&UPPER(LEFT(B12,2))&TEXT(MONTH(C12),"00")&TEXT(DAY(C12),"00")&RIGHT(YEAR(C12),2)&"-"&TEXT(ROW()-11,"0000")
>
> This is using US date style. I could not tell from your sample what date
> style you are using (as 1/6 could be January 6 or June 1.)
>
> The formula started on row 12 (as should be obvious) which is why I
> subtracted 11 from row() (to begin on 1.) Modify to suit your needs.
>
> "cxlough41" wrote:
>
> > i am trying to create a workbook which the first page is the customer
> > information page. this is done... now i need to use the first two letters
> > from each cell containing the first and last names... now i need to add the
> > current date in ddmmyy format NO PUNCTUATION...followed by a dash and 0000
> > which will increase by 1 each time the name cells are filled in. i would
> > also like to add auto "save as" with this generated number as the name of the
> > work book. every customer gets a new work book for each job ( 1 cust / 10
> > jobs = 10 work books )
> >
> > this is what i am looking for;
> >
> > customer name is Will I. Twirk
> > he calls for service on 01/06/05
> > after confirming his interest we create a new work book for the new customer
> > I open the work book template
> > I add customers last name
> > I add cust first name
> > when i press tab next... i want the number to generate and show up as
> > TWWI010605-0001
> >
> > can this happen??? HELLLPPPP!!!!!
> >
> > I don't want to push my luck but..... it would be really nice if this number
> > could be sent to the invoice page to create the same number but with an I
> > just be4 the dash.
> >
> > and if i have not figured by then how to do the same for the estimate page
> > with an E instead of an I ...... what the heck can you show that too???
> > pleeeeease heeeeellllp.
> >
> > i really need to be able to track by name so this seems like it would really
> > suit my needs

[pinmaster]

ROW(1:1) is not dependent on the current row, you can put it anywhere and it will return a 1, as oppose to ROW()-11 which has to be put somewhere in row 12 to return a 1. You could also go with ROW(A1) or any cell address in row 1.

Regards
JG

I think i know what this small change does but I am not absolutely sure.
Could you send a reply to verify what this change will do.

Thank you so much!

"pinmaster" wrote:

>
> Instead of
> TEXT(ROW()-11,"0000")
> you could change it to
> TEXT(ROW(1:1),"0000")
> that way no matter where you start your formula it will always return a
> 1 in the first cell.
>
> Regards
> JG
>
>
> --
> pinmaster
> ------------------------------------------------------------------------
> pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
> View this thread: http://www.excelforum.com/showthread...hreadid=496864
>
>

[cxlough41]

Thanks for the clarification i think this will add better functionality...
could you please review my newer posts to see if you can help with the date
question...

or... i need to add a current date to a cell (can be hidden or not) but the
date can not be volitile. I understand that using =(today) will give date
but not sure how to prevent the date cell from updateing on each opening of
the file.

i could just enter it manualy but i'm looking for optimized automation for
time use purposes.

thank you so much for your help.


"pinmaster" wrote:

>
> ROW(1:1) is not dependent on the current row, you can put it anywhere
> and it will return a 1, as oppose to ROW()-11 which has to be put
> somewhere in row 12 to return a 1. You could also go with ROW(A1) or
> any cell address in row 1.
>
> Regards
> JG
>
>
>
>
> cxlough41 Wrote:
> > I think i know what this small change does but I am not absolutely
> > sure.
> > Could you send a reply to verify what this change will do.
> >
> > Thank you so much!
> >
> > "pinmaster" wrote:
> >
> > >
> > > Instead of
> > > TEXT(ROW()-11,"0000")
> > > you could change it to
> > > TEXT(ROW(1:1),"0000")
> > > that way no matter where you start your formula it will always return
> > a
> > > 1 in the first cell.
> > >
> > > Regards
> > > JG
> > >
> > >
> > > --
> > > pinmaster
> > >
> > ------------------------------------------------------------------------
> > > pinmaster's Profile:
> > http://www.excelforum.com/member.php...fo&userid=6261
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=496864
> > >
> > >
>
>
> --
> pinmaster
> ------------------------------------------------------------------------
> pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
> View this thread: http://www.excelforum.com/showthread...hreadid=496864
>
>

[pinmaster]

You could just hit CTRL+; to insert the current date into a cell, it's not automatic but it's quicker. Other than that you would need VBA, I think I saw a few post recently on the subject, just do a search on "automatic date entry" or something similar and I'm sure you will find something.

Regards
JG

[pinmaster]

Have a look at:

http://www.mcgimpsey.com/excel/timestamp.html

Regards
JG

[cxlough41]

i think i found the answer to my other parts... thought i would let you see
what i came up with... here goes.

if all pages contain the same header in which mine do in this format or soso

My company name date

last name customer no.
first name

i can create a cell on other pages headers that contain the 1st pages
formulas and add &"E" for estimate no. or &"I" for invoice or so on and so
on. the numbers generated look like this

MIBU010605-0001E

which is far more than i thought i would ever get...lol

thanks again for the help

"Kleev" wrote:

> I can't help you with the rest of your question, but a formula to create the
> text you asked for, along with some test data I used is:
> Twirk Will 1/6/2005 TWWI010605-0001
> Twirk Will 1/6/2005 TWWI010605-0002
> Kirk James 7-Jan KIJA010705-0003
> White Vanna 3/1/2005 WHVA030105-0004
> Of the Apes Tarzan 12/30/2005 OFTA123005-0005
>
> UPPER(LEFT(A12,2))&UPPER(LEFT(B12,2))&TEXT(MONTH(C12),"00")&TEXT(DAY(C12),"00")&RIGHT(YEAR(C12),2)&"-"&TEXT(ROW()-11,"0000")
>
> This is using US date style. I could not tell from your sample what date
> style you are using (as 1/6 could be January 6 or June 1.)
>
> The formula started on row 12 (as should be obvious) which is why I
> subtracted 11 from row() (to begin on 1.) Modify to suit your needs.
>
> "cxlough41" wrote:
>
> > i am trying to create a workbook which the first page is the customer
> > information page. this is done... now i need to use the first two letters
> > from each cell containing the first and last names... now i need to add the
> > current date in ddmmyy format NO PUNCTUATION...followed by a dash and 0000
> > which will increase by 1 each time the name cells are filled in. i would
> > also like to add auto "save as" with this generated number as the name of the
> > work book. every customer gets a new work book for each job ( 1 cust / 10
> > jobs = 10 work books )
> >
> > this is what i am looking for;
> >
> > customer name is Will I. Twirk
> > he calls for service on 01/06/05
> > after confirming his interest we create a new work book for the new customer
> > I open the work book template
> > I add customers last name
> > I add cust first name
> > when i press tab next... i want the number to generate and show up as
> > TWWI010605-0001
> >
> > can this happen??? HELLLPPPP!!!!!
> >
> > I don't want to push my luck but..... it would be really nice if this number
> > could be sent to the invoice page to create the same number but with an I
> > just be4 the dash.
> >
> > and if i have not figured by then how to do the same for the estimate page
> > with an E instead of an I ...... what the heck can you show that too???
> > pleeeeease heeeeellllp.
> >
> > i really need to be able to track by name so this seems like it would really
> > suit my needs

[Kleev]

I did not know about this. I'll try to incorporate it into future formulas.
Thanks.

"pinmaster" wrote:

>
> Instead of
> TEXT(ROW()-11,"0000")
> you could change it to
> TEXT(ROW(1:1),"0000")
> that way no matter where you start your formula it will always return a
> 1 in the first cell.
>
> Regards
> JG
>
>
> --
> pinmaster
> ------------------------------------------------------------------------
> pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
> View this thread: http://www.excelforum.com/showthread...hreadid=496864
>
>