[mso] Re: Excel 2003 Date Series

You could certainly put a formula into the 2nd and subsequent that took the 
date cell from the previous sheet and added 1 to it.  You'd need to do this 
separately for each sheet, but it'd be set thereafter and all you'd need to 
do is change the first cell.

Alternatively, if the sheet's name includes the day number of the month, you 
could grab the month and year from the first sheet, or a summary sheet, or 
the file name, and attach the day number from the sheet name.

This formula will return the name of the worksheet that the cell it's in is 
on:

  =MID(CELL("filename"),FIND("]",CELL("filename"))+1,256)

In sheet 1 I have A1 set to 1 Sept 2008

I have two sheets called 12 and 13

In both, the formula

  
=DATE(YEAR(Sheet1!$A$1),MONTH(Sheet1!$A$1),MID(CELL("filename"),FIND("]",CELL("filename"))+1,256))

gives me 12/9/08 and 13/9/08 respectively.

Note that this will not work until after the workbook has been saved.

You could also use a small bit of VBA to grab the sheet name (not dependent 
on whether the workbook has been saved or not) and potentially to do the 
whole date calculation.  E.g.

  Option Explicit

  Public Function SheetDate(ByVal MonthYear As Date, ByRef CellReference As 
Range) As Date
    SheetDate = DateValue(Year(MonthYear) & "/" & Month(MonthYear) & "/" & 
CellReference.Worksheet.Name)
  End Function

Then call it by

  =SheetDate(Sheet1!$A$1, $A$1)

Note that both A1 cells should preferably be fully anchored with $ so that 
you can copy the formula around without worry.

Regards, Dave S

----- Original Message ----- 
From: "Cheryl Day" <cday15@xxxxxxx>
To: <mso@xxxxxxxxxxxxx>
Sent: Wednesday, February 21, 2007 9:57 AM
Subject: [mso] Excel 2003 Date Series


>I have a workbook with 31 worksheets representing each day of the month.
> Presently, I am manually entering the mm/dd/yyyy in the same cell on each
> worksheet (e.g., first worksheet has 1/1/07 in G1, second worksheet has
> 1/2/07 in G1, etc).
>
>
> I thought that maybe I could group the worksheets and use the Date Series
> AutoFill, but grouping tends to put the same date in each worksheet.
> AutoFill seems available only to adjacent cells and not between 
> worksheets.
>
>
>
> I looked at Date Functions but none seems appropriate.
>
>
>
> Can anyone help me?
>
>
>
> Thanks, CDay
>
>
>
>
>
>
>
>
>
> *************************************************************
> You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
> MicrosoftOffice@xxxxxxxxxxxxxxxx
>
> To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
>
> To Unsubscribe from this group, visit the group's homepage and use the 
> dropdown menu at the top.  This will allow you to unsubscribe your email 
> address or change your email settings to digest or vacation (no mail).
> http://www.freelists.org/webpage/mso
>
> If you are using Outlook and you see a lot of unnecessary code in your 
> email messages, read these instructions that explain why and how to fix 
> it:
> http://personal-computer-tutor.com/abc3/v28/greg28.htm
> ************************************************************* 

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top.  This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************

Other related posts: