software

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:
=quot;PF-quot;amp;TEXT('Front Sheet'!$D$6,quot;ddmmquot;)amp;TEXT(ROW(A1),quot;00quot;)
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:
=quot;PF-quot;amp;TEXT('Front Sheet'!$D$6,quot;ddmmquot;)amp;TEXT(COLUMN(A1),quot;00quot;)
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
savefile.com/projects/236895
--
quot;MikeR-Ozquot; gt; wrote in message
...
gt; I have a excel spreadsheet that has a front sheet with a date that I type
in
gt; . this then is populate across the other work sheets within the
spreadsheet,
gt; such that formula ='Front Sheet'!D6 3 being worksheets Monday , Tuesday
etc
gt; hence the first date on 'Front Sheet' and the add (number) for the week
days.
gt;
gt; NOW I would lie to put into another cell a code tha always starts PF- and
gt; the rest of it PF-121201 being the day, month and then numbers01 and
upwards
gt; to about 15 across the worksheets. Now I would like to get the numerical
gt; part 121201to be partly derived from the initial date on the 'front sheet'
..
gt; Can this be done to the cell - how do I refernce it?
gt;
gt; 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 gt;gt; 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

quot;Maxquot; wrote:

gt; Just venturing some guesses here,
gt; maybe something along these lines might be a start ..
gt;
gt; In sheet: Front Sheet
gt; we have a date in D6: 12-Dec-2005
gt;
gt; Then in another sheet, say Sheet2:
gt; we could put in say, A2:
gt; =quot;PF-quot;amp;TEXT('Front Sheet'!$D$6,quot;ddmmquot;)amp;TEXT(ROW(A1),quot;00quot;)
gt; and copy A2 down, which yields:
gt;
gt; PF-121201
gt; PF-121202
gt; PF-121203
gt; PF-121204
gt; etc
gt;
gt; Or, if we wanted to increment it copying across,
gt; we could put in say B1:
gt; =quot;PF-quot;amp;TEXT('Front Sheet'!$D$6,quot;ddmmquot;)amp;TEXT(COLUMN(A1),quot;00quot;)
gt; and copy B1 across
gt;
gt; Formula above is the same as the preceding
gt; except that COLUMN(A1) replaces ROW(A1)
gt; (for incrementing the last 2 digits as we copy across)
gt; --
gt; Rgds
gt; Max
gt; xl 97
gt; ---
gt; Singapore, GMT 8
gt; xdemechanik
gt; savefile.com/projects/236895
gt; --
gt; quot;MikeR-Ozquot; gt; wrote in message
gt; ...
gt; gt; I have a excel spreadsheet that has a front sheet with a date that I type
gt; in
gt; gt; . this then is populate across the other work sheets within the
gt; spreadsheet,
gt; gt; such that formula ='Front Sheet'!D6 3 being worksheets Monday , Tuesday
gt; etc
gt; gt; hence the first date on 'Front Sheet' and the add (number) for the week
gt; days.
gt; gt;
gt; gt; NOW I would lie to put into another cell a code tha always starts PF- and
gt; gt; the rest of it PF-121201 being the day, month and then numbers01 and
gt; upwards
gt; gt; to about 15 across the worksheets. Now I would like to get the numerical
gt; gt; part 121201to be partly derived from the initial date on the 'front sheet'
gt; ..
gt; gt; Can this be done to the cell - how do I refernce it?
gt; gt;
gt; gt; Mike
gt;
gt;
gt;

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=quot;quot;,quot;quot;,quot;PF-quot;amp;TEXT('Front Sheet'!$D$6,quot;ddmmquot;)amp;quot;00quot;)

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=quot;quot;,quot;quot;,LEFT($A$1,LEN($A$1)-2)amp;TEXT(ROW(A1),quot;00quot;))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 quot;blankquot;, and
A4:A6 will also appear quot;blankquot;
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--
quot;MikeR-Ozquot; gt; wrote in message
...
gt; Max, Thats great - a major step forward for for me. Now, can it instead
of
gt; copying down or across and having it incrimentally add the next number or
be
gt; individually setup as a template with each cell formula will gt;gt; say cell
A1
gt; has the formula to give the PF-231200 and then cell A4 will populate or
have
gt; formula that gives PF-231201 and cell A6 PF-231203 etc .
gt;
gt; Mike
Oops, this part below should have appeared in the post as:

gt; .. And then put in A4 :
gt; =IF($A$1=quot;quot;,quot;quot;,LEFT($A$1,LEN($A$1)-2)amp;TEXT(ROW(A1),quot;00quot;))
gt; Copy A4 down to say A6

(The last line got wrapped around to the formula line)
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--
Thanks Max, Works a treat. Appreciate your time end effort - have a great
Chrissy / New Year.
Cheers
Mike

quot;Maxquot; wrote:

gt; Oops, this part below should have appeared in the post as:
gt;
gt; gt; .. And then put in A4 :
gt; gt; =IF($A$1=quot;quot;,quot;quot;,LEFT($A$1,LEN($A$1)-2)amp;TEXT(ROW(A1),quot;00quot;))
gt; gt; Copy A4 down to say A6
gt;
gt; (The last line got wrapped around to the formula line)
gt; --
gt; Rgds
gt; Max
gt; xl 97
gt; ---
gt; Singapore, GMT 8
gt; xdemechanik
gt; savefile.com/projects/236895
gt; --
gt;
gt;
gt;

Glad to hear that, Mike !
Thanks for feedback and wishes ..
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--
quot;MikeR-Ozquot; gt; wrote in message
...
gt; Thanks Max, Works a treat. Appreciate your time end effort - have a great
gt; Chrissy / New Year.
gt; Cheers
gt; Mike