If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
HOW ?Excel chart auto insert /populate a code based on date
I have a excel spreadsheet that has a front sheet with a date that I type in
.. this then is populate across the other work sheets within the spreadsheet, such that formula ='Front Sheet'!D6+3 being worksheets Monday , Tuesday etc hence the first date on 'Front Sheet' and the add (number) for the week days. NOW I would lie to put into another cell a code tha always starts PF- and the rest of it PF-121201 being the day, month and then numbers01 and upwards to about 15 across the worksheets. Now I would like to get the numerical part 121201to be partly derived from the initial date on the 'front sheet' . Can this be done to the cell - how do I refernce it? Mike |
#2
|
|||
|
|||
HOW ?Excel chart auto insert /populate a code based on date
Just venturing some guesses here,
maybe something along these lines might be a start .. In sheet: Front Sheet we have a date in D6: 12-Dec-2005 Then in another sheet, say Sheet2: we could put in say, A2: ="PF-"&TEXT('Front Sheet'!$D$6,"ddmm")&TEXT(ROW(A1),"00") and copy A2 down, which yields: PF-121201 PF-121202 PF-121203 PF-121204 etc Or, if we wanted to increment it copying across, we could put in say B1: ="PF-"&TEXT('Front Sheet'!$D$6,"ddmm")&TEXT(COLUMN(A1),"00") and copy B1 across Formula above is the same as the preceding except that COLUMN(A1) replaces ROW(A1) (for incrementing the last 2 digits as we copy across) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "MikeR-Oz" wrote in message ... I have a excel spreadsheet that has a front sheet with a date that I type in . this then is populate across the other work sheets within the spreadsheet, such that formula ='Front Sheet'!D6+3 being worksheets Monday , Tuesday etc hence the first date on 'Front Sheet' and the add (number) for the week days. NOW I would lie to put into another cell a code tha always starts PF- and the rest of it PF-121201 being the day, month and then numbers01 and upwards to about 15 across the worksheets. Now I would like to get the numerical part 121201to be partly derived from the initial date on the 'front sheet' .. Can this be done to the cell - how do I refernce it? Mike |
#3
|
|||
|
|||
HOW ?Excel chart auto insert /populate a code based on date
Max, Thats great - a major step forward for for me. Now, can it instead of
copying down or across and having it incrimentally add the next number or be individually setup as a template with each cell formula will say cell A1 has the formula to give the PF-231200 and then cell A4 will populate or have formula that gives PF-231201 and cell A6 PF-231203 etc . Mike "Max" wrote: Just venturing some guesses here, maybe something along these lines might be a start .. In sheet: Front Sheet we have a date in D6: 12-Dec-2005 Then in another sheet, say Sheet2: we could put in say, A2: ="PF-"&TEXT('Front Sheet'!$D$6,"ddmm")&TEXT(ROW(A1),"00") and copy A2 down, which yields: PF-121201 PF-121202 PF-121203 PF-121204 etc Or, if we wanted to increment it copying across, we could put in say B1: ="PF-"&TEXT('Front Sheet'!$D$6,"ddmm")&TEXT(COLUMN(A1),"00") and copy B1 across Formula above is the same as the preceding except that COLUMN(A1) replaces ROW(A1) (for incrementing the last 2 digits as we copy across) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "MikeR-Oz" wrote in message ... I have a excel spreadsheet that has a front sheet with a date that I type in . this then is populate across the other work sheets within the spreadsheet, such that formula ='Front Sheet'!D6+3 being worksheets Monday , Tuesday etc hence the first date on 'Front Sheet' and the add (number) for the week days. NOW I would lie to put into another cell a code tha always starts PF- and the rest of it PF-121201 being the day, month and then numbers01 and upwards to about 15 across the worksheets. Now I would like to get the numerical part 121201to be partly derived from the initial date on the 'front sheet' .. Can this be done to the cell - how do I refernce it? Mike |
#4
|
|||
|
|||
HOW ?Excel chart auto insert /populate a code based on date
Try this amended set-up ..
In sheet: Front Sheet, as before, we have a reference date in D6: 23-Dec-2005 (say) In Sheet2, we could put in A1: =IF('Front Sheet'!D6="","","PF-"&TEXT('Front Sheet'!$D$6,"ddmm")&"00") A1 will return: PF-231200 (If the date in 'Front Sheet'!D6 is cleared, A1 will appear blank) And then put in A4 : =IF($A$1="","",LEFT($A$1,LEN($A$1)-2)&TEXT(ROW(A1),"00"))Copy A4 down to say A6 If A1 returns: PF-231200, A4:A6 will return: PF-231201 PF-231202 PF-231203 And if the date in 'Front Sheet'!D6 is cleared, A1 will be "blank", and A4:A6 will also appear "blank" -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "MikeR-Oz" wrote in message ... Max, Thats great - a major step forward for for me. Now, can it instead of copying down or across and having it incrimentally add the next number or be individually setup as a template with each cell formula will say cell A1 has the formula to give the PF-231200 and then cell A4 will populate or have formula that gives PF-231201 and cell A6 PF-231203 etc . Mike |
#5
|
|||
|
|||
HOW ?Excel chart auto insert /populate a code based on date
Oops, this part below should have appeared in the post as:
.. And then put in A4 : =IF($A$1="","",LEFT($A$1,LEN($A$1)-2)&TEXT(ROW(A1),"00")) Copy A4 down to say A6 (The last line got wrapped around to the formula line) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#6
|
|||
|
|||
HOW ?Excel chart auto insert /populate a code based on date
Thanks Max, Works a treat. Appreciate your time end effort - have a great
Chrissy / New Year. Cheers Mike "Max" wrote: Oops, this part below should have appeared in the post as: .. And then put in A4 : =IF($A$1="","",LEFT($A$1,LEN($A$1)-2)&TEXT(ROW(A1),"00")) Copy A4 down to say A6 (The last line got wrapped around to the formula line) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#7
|
|||
|
|||
HOW ?Excel chart auto insert /populate a code based on date
Glad to hear that, Mike !
Thanks for feedback and wishes .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "MikeR-Oz" wrote in message ... Thanks Max, Works a treat. Appreciate your time end effort - have a great Chrissy / New Year. Cheers Mike |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
More Duplicate E-mails Outlook 2003 | John Smith | General Discussion | 36 | January 8th, 2007 05:36 PM |
Query for 'confirmation' | rogge | Running & Setting Up Queries | 8 | April 19th, 2005 03:26 PM |
error 550 won't deliver | theMooooo | General Discussion | 3 | March 29th, 2005 12:47 PM |
*Another* OLK 2002 sendmail/SMTP problem | steviegb | General Discussion | 11 | March 23rd, 2005 12:59 AM |
0x80040109 error when sending from SSL SMTP | krouse | General Discussion | 7 | March 15th, 2005 01:55 AM |