Fill across several sheets in a workbook

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

I have been unable to set up a formula to increment the
date by one day on succesive work sheets. I can do it
by "hand" and copy the formuala changing the increment on
each cell...

How can I do this without having all this fun...
??

Thanks
 
Since you've had no "takers", let's try!

To accomplish something like this, any formula that you use will:
First - have to know where to look to find the starting date and,
Second - need some sort of reference within each WS, so that it (the
formula) will know where it's at, in relation to the rest of the WSs in the
WB in order to know how much the date needs to be incremented.
Probably, the easiest and simplest location reference to use would be XL's
default sheet names - Sheet1 - Sheet2 ...

Following this concept, if you put the starting date of the WB in A1 of
Sheet1, and this formula is in A1 of each new WS, with the understanding
that each succesive WS will have the default sheet name, you should get what
you're looking for:

=DATE(YEAR(Sheet1!$A$1),MONTH(Sheet1!$A$1),DAY(Sheet1!$A$1)+RIGHT(CELL("file
name",$A$1),1)-1)

This works as long as the WB is *saved*, so that the Cell() function will be
able to return the sheet name.

NOW, this will work for the first nine WSs.
If your WBs are for a week (7 days), then it's OK.

If, however, you're WBs are monthly, with double digit sheet names, the
formula becomes a little longer.
(This longer one will also work for single digit sheets.)

=DATE(YEAR(Sheet1!$A$1),MONTH(Sheet1!$A$1),DAY(Sheet1!$A$1)+IF(ISERR(--RIGHT
(CELL("filename",$A$1),2)),RIGHT(CELL("filename",$A$1),1)-1,RIGHT(CELL("file
name",$A$1),2)-1))

You might be able to revise these formulas to perhaps better suit your needs
(sheet names).

Other folks might also find ways to shorten these!
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I have been unable to set up a formula to increment the
date by one day on succesive work sheets. I can do it
by "hand" and copy the formuala changing the increment on
each cell...

How can I do this without having all this fun...
??

Thanks
 
Doug

Sub Date_Increment()
''increment a date in A1 across sheets
Dim myDate As Date
Dim iCtr As Long
myDate = DateSerial(2004, 4, 30)
For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("A1")
.Value = myDate - 1 + iCtr
.numberformat = "mm/dd/yyyy"
End With
Next iCtr
End Sub

Gord Dibben Excel MVP
 
Thanks...for your reply...
I did try the script in the following post...works great

Doug
 
Thanks,
After looking at the routine you suggested, I may have to
invest in a little VBA traing...the formula approach
seemed long...(Mine did/is for sure).

Doug
 
Back
Top