Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
create self-generating numbers with letters and numbers
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
create self-generating numbers with letters and numbers
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(C 12),"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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
create self-generating numbers with letters and numbers
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
create self-generating numbers with letters and numbers
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(C 12),"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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
create self-generating numbers with letters and numbers
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
create self-generating numbers with letters and numbers
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(C 12),"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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
create self-generating numbers with letters and numbers
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
create self-generating numbers with letters and numbers
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
create self-generating numbers with letters and numbers
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
create self-generating numbers with letters and numbers
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
create self-generating numbers with letters and numbers
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(C 12),"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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
create self-generating numbers with letters and numbers
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
no row numbers or column letters DISPLAYED | Excel Discussion (Misc queries) | |||
How do I change format of page numbers to letters in Excel? | Excel Discussion (Misc queries) | |||
Letters = Numbers | Charts and Charting in Excel | |||
How do I sort letters before numbers in Excel? | Excel Discussion (Misc queries) | |||
VLOOKUP for a cell with both letters and numbers | Excel Discussion (Misc queries) |