Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 21st 05, 04:54 AM posted to microsoft.public.excel.newusers
MikeR-Oz
 
Posts: n/a
Default 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   Report Post  
Old December 21st 05, 09:20 AM posted to microsoft.public.excel.newusers
Max
 
Posts: n/a
Default 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   Report Post  
Old December 22nd 05, 06:32 AM posted to microsoft.public.excel.newusers
MikeR-Oz
 
Posts: n/a
Default 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   Report Post  
Old December 22nd 05, 07:42 AM posted to microsoft.public.excel.newusers
Max
 
Posts: n/a
Default 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   Report Post  
Old December 22nd 05, 07:56 AM posted to microsoft.public.excel.newusers
Max
 
Posts: n/a
Default 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   Report Post  
Old December 22nd 05, 12:11 PM posted to microsoft.public.excel.newusers
MikeR-Oz
 
Posts: n/a
Default 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   Report Post  
Old December 22nd 05, 12:16 PM posted to microsoft.public.excel.newusers
Max
 
Posts: n/a
Default 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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date updates from worksheet to chart & changes date to a date series! Help!! Jayjg Charts and Charting in Excel 2 January 22nd 05 03:00 PM
Line chart - date line association gone mad! Johannes Czernin Charts and Charting in Excel 5 January 17th 05 08:48 PM
Challenging Charting C TO Charts and Charting in Excel 0 January 17th 05 06:57 PM
Show a date based on today DJ Dusty Excel Worksheet Functions 2 November 12th 04 03:20 AM
Chart question insert Alfred Excel Worksheet Functions 2 November 4th 04 08:03 PM


All times are GMT +1. The time now is 08:28 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017