software

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 quot;save asquot; 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:
TwirkWill1/6/2005TWWI010605-0001
TwirkWill1/6/2005TWWI010605-0002
KirkJames7-JanKIJA010705-0003
WhiteVanna3/1/2005WHVA030105-0004
Of the ApesTarzan12/30/2005OFTA123005-0005

UPPER(LEFT(A12,2))amp;UPPER(LEFT(B12,2))amp;TEXT(MONTH(C 12),quot;00quot;)amp;TEXT(DAY(C12),quot;00quot;)amp;RIGHT(YEAR(C12),2)amp;quot;-quot;amp;TEXT(ROW()-11,quot;0000quot;)

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.

quot;cxlough41quot; wrote:

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


Instead of
TEXT(ROW()-11,quot;0000quot;)
you could change it to
TEXT(ROW(1:1),quot;0000quot;)
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: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=496864this 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: quot;I don't want
to push my luck but..... it would be really nice if this number
gt; gt; could be sent to the invoice page to create the same number but with an I
gt; gt; just be4 the dash.
gt; gt;
gt; gt; and if i have not figured by then how to do the same for the estimate page
gt; gt; with an E instead of an I ...... what the heck can you show that too???quot;

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

Tanks so much!quot;Kleevquot; wrote:

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

quot;pinmasterquot; wrote:

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

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!

quot;Kleevquot; wrote:

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


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:
gt; I think i know what this small change does but I am not absolutely
gt; sure.
gt; Could you send a reply to verify what this change will do.
gt;
gt; Thank you so much!
gt;
gt; quot;pinmasterquot; wrote:
gt;
gt; gt;
gt; gt; Instead of
gt; gt; TEXT(ROW()-11,quot;0000quot;)
gt; gt; you could change it to
gt; gt; TEXT(ROW(1:1),quot;0000quot;)
gt; gt; that way no matter where you start your formula it will always return
gt; a
gt; gt; 1 in the first cell.
gt; gt;
gt; gt; Regards
gt; gt; JG
gt; gt;
gt; gt;
gt; gt; --
gt; gt; pinmaster
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; pinmaster's Profile:
gt; www.excelforum.com/member.php...foamp;userid=6261
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=496864
gt; gt;
gt; gt;--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=496864Thanks 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.quot;pinmasterquot; wrote:

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


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
quot;automatic date entryquot; or something similar and I'm sure you will find
something.

Regards
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=496864
Have a look at:

www.mcgimpsey.com/excel/timestamp.html

Regards
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=496864