Formulas over multiple worksheets

  • Thread starter Thread starter HelpDeskChick
  • Start date Start date
H

HelpDeskChick

Is it possible to apply a formula to multiple worksheets? The scenario
a user has 52 worksheets, one for each week of the year. In A1 of th
first worksheet, she's entered a date. She'd like to use a formula t
title A1 in each of the 52 worksheets like A1+7, A1+14...etc. so sh
doesn't have to enter 52 titles
 
Well,my stupid way goes like this:
On Sheet2, A1, enter:
=Sheet1!A1+7
On Sheet3,A1,enter:
=Sheet1!A1+14
...
The initial time can be done manually or via vba codes(sorry I don'
know how), but afterwards, she can change all the sheets by just chang
the dates on sheet1
 
This thread has both a formula and a code solution.

http://tinyurl.com/2aml6

--

HTH,

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


message Is it possible to apply a formula to multiple worksheets? The scenario:
a user has 52 worksheets, one for each week of the year. In A1 of the
first worksheet, she's entered a date. She'd like to use a formula to
title A1 in each of the 52 worksheets like A1+7, A1+14...etc. so she
doesn't have to enter 52 titles.
 
Help Desk Chick,

I'll give you my standard blurb on having data in separate sheets. You can
explain this to the user who will likely (all of the following): 1) glaze
over and possibly not bother you again for quite a while, 2) continue to do
what he's doing with the multiple sheets, and 3) realize later on the wisdom
of your message and kneel at your feet.

Blurb on separating similar data across sheets:

There's a lot of Excel functionality that isn't available when similar data
is spread across multiple sheets, as well as across workbooks. Questions
abound where users already have data in separate sheets, and now want to
find certain data, summarize the data, etc. and there are no direct means to
do that.

If the layout of the data in the sheets will be the same (same column
headings), it is generally best to put all the data in a single sheet, with
an additional column for what originally was the various sheets. For
example, if you have a sheet for each month, put all the data in a single
sheet, with an additional column for month. An Autofilter can easily
reduce this consolidated sheet to the equivalent of one of the original
(month) sheets. Now you can sort in various useful ways, use Data -
Subtotals, easily make a pivot table to summarize the data, use database
functions (DSUM, COUNTIF, etc.).

If the separate sheets already exist, it's a straightforward one-time
project to combine them. Just make a sheet with the extra (month) column.
Now paste the records from the first sheet, and enter Jan into the month
column and copy down with the fill handle or copy/paste. Repeat for the
other sheets.
 
You could use a small macro that populates A1 of each sheet:

Option Explicit
Sub testme()
Dim StartDate As Date
Dim iCtr As Long

StartDate = DateSerial(2005, 1, 1)

For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("a1")
.Value = StartDate + (iCtr - 1) * 7
.NumberFormat = "mm/dd/yyyy"
End With
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top