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

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

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
1 in the first cell.

Regards
J

--
pinmaste
-----------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...nfo&userid=626
View this thread: http://www.excelforum.com/showthread.php?threadid=49686

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

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

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

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

Regards
JG




cxlough41 Wrote:
> I think i know what this small change does but I am not absolutel
> 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 retur
> 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
> >
>

--
pinmaste
-----------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...nfo&userid=626
View this thread: http://www.excelforum.com/showthread.php?threadid=49686

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

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
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=496864

Have a look at:

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

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