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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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

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

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" <[email protected]> wrote in message
news:[email protected]...
> Thanks Max, Works a treat. Appreciate your time end effort - have a great
> Chrissy / New Year.
> Cheers
> Mike