HOW ?Excel chart auto insert /populate a code based on date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
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
 
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"
 
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
Click to expand...

(The last line got wrapped around to the formula line)
 
Thanks Max, Works a treat. Appreciate your time end effort - have a great
Chrissy / New Year.
Cheers
Mike
 
Back
Top