Formulas over multiple worksheets

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

--
Message posted from http://www.ExcelForum.com

0
6/18/2004 8:15:55 PM
excel 39879 articles. 2 followers. Follow

4 Replies
307 Views

Similar Articles

[PageSpeed] 35

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

--
Message posted from http://www.ExcelForum.com

0
6/18/2004 8:53:22 PM
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!
==============================================


"HelpDeskChick >" <<HelpDeskChick.182c0h@excelforum-nospam.com> wrote in
message news:HelpDeskChick.182c0h@excelforum-nospam.com...
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.


---
Message posted from http://www.ExcelForum.com/

0
ragdyer1 (4060)
6/18/2004 10:34:44 PM
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.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"HelpDeskChick >" <<HelpDeskChick.182c0h@excelforum-nospam.com> wrote in
message news:HelpDeskChick.182c0h@excelforum-nospam.com...
> 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.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
nowhere1083 (630)
6/19/2004 7:01:43 PM
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

"HelpDeskChick <" wrote:
> 
> 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.
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/10/2004 11:40:58 PM
Reply:

Similar Artilces:

How to find multiple same char in a string in Excel?
Hello: I am trying to locate the last subdirectory name from a full path. For example, I have a full path like: "c:\temp\temp1\temp2" I would like to find the temp2 which is the last subdirectory. I am thinking to use find() to find the last "\", and then use left(), right() or mid() to obtain it. The problem is, when I tried to use find() function, it only returns the first position of the "\" in the full path string. How can I get the last "\" position in the string? p.s., I had searched the Internet, but all the examples I found wer...

Amend formula?
Hi all, I have the following formula which works well: SUMPRODUCT((Main!$G$4:$G$2885 = "I")*(Main!T4:T2885)) but i want to amend it slightly to check if column B equals GL as well - think that is: (Main!$B$4:$B$2885 = "GL") but i don't know how!! Thanks, Kirstie SUMPRODUCT works by multiply numbers (row-by-row) and then adding those individual products up. Your numbers can be actual values and/or logical expressions. The reason your formula works is because the logical expression (Main!$G$4:$G$2885 = "I") evaluates to either TRUE or FALSE whi...

Search for mail in multiple PST files
I have about 90 pst files that I need to search for mail with about a dozen terms and phrases. Outlook will only allow you to search one pst at a time, and I've tried Google Desktop and MS Desktop Search and they are not effectively indexing all the emails, but even if they did I cannot effectively export the results to a file. Does anyone know of a utility or method that would work best for this? http://office.microsoft.com/en-us/outlook/HA011415261033.aspx or http://zyfind.com/products_technology/productsheets/ZyFIND%20for%20Outlook.pdf -- John Oliver, Jr MCSE, MCT, CCNA Exchange...

Checkbox Formula
Can anyone see what the problem with this formula might be? The formula refers to a checkbox value on another sheet (Commission Pool) and a value from even another sheet (Commission Level) all to go into the selected/current sheet (Leasing_1) =IF('Commission Pool'!CheckBox.1.value=TRUE,'Commission Level'!I18,"") -- Randy Street Rancho Cucamonga, CA You can't get the value of a checkbox directly using a formula that points at that checkbox. But checkboxes can have linked cells that will be true or false. So maybe you can go back to that chec...

When I add a row to excel how can I stop formulae changing
I've done this before, but can't remember how. I want to add a row at the top of a sheet, but without formulae with a range eg =sum(a1:a5) changing to =sum(a2:a6). $ isn't the answer =SUM(INDIRECT("A1:A5")) HTH Kostis Vezerides "vezerid" wrote: > =SUM(INDIRECT("A1:A5")) > > HTH > Kostis Vezerides > Thanks for that. How about if I after inserting a row A1:A5 changes to A1:A6 TIA Dave ...

Autopopulate Multiple Fields in Table Using a Form
I have a form based on a table where I want to store data. The data to store comes from combo boxes based on various lookup tables. The wrinkle is that there is one lookup table that contains 2 fields of data (questionnum and question) I want stored in the table once the questionnum is selected. The question field is formatted as 'memo'. When I create the combo box the question field is not an option for selection. I add it manually to the properties after completing the criteria for the combo box. I have been struggling with the code used to autopopulate more than one field but...

Copy worksheets and formats
Hi, I have a worksheet (budget) set up as 31 columns representing days of a month. I also have a worksheet designated as yearly, 12 columns for each month. The first worksheet, Jan, totals numerous rows and sends the data to the Jan column of the monthly WS. I need to copy the Jan worksheet in a way it will change the Jan! to Feb!, then Mar!, etc. If I copy it as is the data will be the same as the month of Jan. Hope you can understand this. I can't ;+) Ken More than one way, but an easy way if each month formulas are the same is to copy Jan sheet, and paste them into the Feb a...

invoicing with multiple bin
Why invoicing can not be used while using multiple bin in inventory? what does it mean? thanks Ala’a, Unfortunately invoicing module simply does not support inventory bins, you need either to disable your inventory bins from “Microsoft Dynamics GP menu >> Setup >> Inventory >> Inventory Control” “Enable Multiple Bins checkbox” or to use Sales Order Processing instead to generate your invoices. Regards, -- Mohammad R. Daoud MCP, MCBMSP, MCTS, MCBMSS Mob: +962 - 79 -999 65 85 Great Package For Business Solutions daoudm@greatpbs.com http://www.greatpbs.com http://mohda...

formula discount
how to use formula discount? "%" "Discount" can mean a lot of things. One meaning would be to give an x% discount on a base value. If the base value is in A1 and you want to apply a 10% discount: B1: =A1 * (1 - 10%) or, equivalently B1: =A1 * 90% In article <9CF9AB77-C659-4CD5-A991-42D83FD4DBDE@microsoft.com>, "need formula as soon as posible" <need formula as soon as posible@discussions.microsoft.com> wrote: > how to use formula discount? "%" Or are talking about the DISC worksheet function. You can find the help on ...

Problem using fill with formulas
I have a column. I want to fill in the formula from B1 all the way down to B200. I select B1 and drag my mouse down to B200 and select Fill Down. The formula is inserted in every cell. However, I have: =VLOOKUP(A4,[yahoo.xls]Sheet1!$A1:$H200,2,FALSE) =VLOOKUP(A5,[yahoo.xls]Sheet1!$A2:$H201,2,FALSE) when I want the second column formula to be: =VLOOKUP(A4,[yahoo.xls]Sheet1!$A1:$H200,2,FALSE) In other words, I want the first cell reference (A) to change, but not the cell references in the yahoo.xls worksheet. Because they are changing, I need to go in and manually change the references to $A...

Selecting A single Month From a Table with Multiple Months
The following code is used as the criteria in a selct query for a date field: >=DateSerial(Year(Date()),Month(Date())-1,1) And <=DateSerial(Year(Date()),Month(Date()),0) It has worked fine until the table included records from January 2010, database was created in 2009. Is there a fix or a better way to accomplish the intent? Not sure what your problem is the expressions you have return the first and last day of the prior month. So this is February 3, 2010 and the expression return 1/1/2010 and 1/31/2010. What do you want returned? What is the problem you are ha...

Print multiple pages per Sheet in Excel 2003
I have a spreadsheet with 12 rows that goes for many columns ( with formulas). Is there a way to print this where three pages are on each piece of paper? How about inserting a new worksheet, then copying and pasting (as values) to that new sheet. Then print that "helper" worksheet. If you have to do this lots, this may be worth the trouble. Insert a new sheet. select a nice range on the original sheet Edit|copy go to the new worksheet shift-edit|Paste picture link Repeat so that you have pictures of the ranges so it prints the way you want. Then print this helper worksheet. Th...

Use Formula in DGET criteria
Hi there, I'm getting an error with a DGET function. One of the criteria fields is a formula (an IF statement) which I assume is the problem. Is there any way round this i.e. being able to change the criteria based on the contents of another cell? Many thanks - David ...

Multiple Pivot Tables based on the same data
I have two pivot tables based on the same source. However, each table is unique in how it shows the date. The first shows all thirty days of the month. The second groups the date in 10 day increments. The first table is summed data while the other is averaged data. When I try to set up the second table to group, it also groups the first table even though its' on a different sheet. I remember being able to do this in version 2003. What have I overlooked? Thanks Phil In xl2003, I could create the pivottables as separate entities. I created the first PT and then sta...

Use of wildcards in formulae
HI I need a little advice. I'm trying to say that if C6 starts with the letter 'C' , then put 'Yes' , if not put 'No' I'm using this formula : =IF(C6="C*","Yes","No") but it's not giving the correct responses. Can someone advise? Thanks Hi Colin Try this =IF(COUNTIF(C6,"C*")>0,"Yes","No") HTH John "Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message news:tVEQEIAXJ2bLFwKd@chayes.demon.co.uk... > > HI > > I need a little advice. &g...

IF Formula #2
I need to set up an IF formula for a series on account numbers- the first 4 digits of the # are the same, the last 2 change but all are included in the answer. I have several series of numbers to include: example: 1234-01 through 1234-99 and 2007-01 through 2007-99 and 3010-01 through 3010-99 Any help you could give me would be appreciated What would you like the formula to do? "ESSO" wrote: > I need to set up an IF formula for a series on account numbers- the first 4 > digits of the # are the same, the last 2 change but all are included in the > answer. I have se...

Excel Sum with multiple (over 2 criteria)
I've been trying to get this to work for several hours and am at my wits ends. I have a data table with one column of numberical data that i need to sum based upon three or more critria contained in other columns. The data represents investments and their market values. I have named each of the columns with a range name. THe critria as described by their range names and examples of each are : Country - USA, Great Britain, Japan, etc. Currency - USD, EUR, GBP, JPY Type - Bond, Loan, Equity long_short - Long, Short I will do many different formulas once I have one that works. ...

Formula calculation in a Shared Excel File
I have a Shared excel workbook which has data entered though the month by multiple people, in this file I have a number of formulas producing data about the entries. The issue I am facing is that when any of the users open or save the new entries the file takes quite a while to re-calculate the formulas. I know I can use the Calculation tab in options to manually calculate the fields but this only works at an individual level rather than in the actual workbook and it also will apply to evey excel file we work on. Does anyone know a solution to have a selected range of cells with formua...

Create individual worksheets for selected rows in a table
Hi guys, I hope somebody may be able to help. I want to be able to automate the following procedure Create individual worksheets for selected rows in a table. Many thanks - Batman2002 Batman, If you really want the entirerow, then use this - if you just want the selection, then remove the .EntireRow of the last line: Sub CopySelectioToNewWorksheet() Dim mySheet1 As Worksheet Dim mySheet2 As Worksheet Set mySheet1 = ActiveSheet Set mySheet2 = Sheets.Add(Type:="Worksheet") mySheet1.Activate Selection.EntireRow.Copy mySheet2.Range("A1") End Sub HTH, Bernie "...

Excel worksheet saved as web page--trouble printing
I have an worksheet that uses a graphic for a background, which I hav also saved in HTML using the Save as a Web Page option. The web pag looks great--but just like the excel background graphic, th "background" graphic in the webpage won't print out for me. Is there any way to fix this so that the graphic will print when print the web page? Thanks--Ki -- Message posted from http://www.ExcelForum.com check the background printing options within the tools, options menu and also the printer properties. hope that helped. >-----Original Message----- >I have an workshee...

Copy same formula to all worksheets in a workbook
Hello guys! I have a table with some formulas saved in workbook "A". Once a month, I receive workbook "B" and it has several worksheets in it (sometimes almost a thousand). I need to copy the table with formulas from workbook "A" and paste it into every-single-worksheet in workbook "B". In the same cell address (C25 for example). As of today, I copy the table and then I go to each worksheet to paste it but I am pretty sure there is a way to do it with a macro or a instruction and save me all that pasting over and over and over again... :S...

Locking Formulas #3
Is there a way to lock formuals in a worksheet so that users can enter new data without accidently dleleting the formulas? You may unlock the desired cells and then protect the worksheet. See "protection" in Help. Lisa wrote: > Is there a way to lock formuals in a worksheet so that users can enter new > data without accidently dleleting the formulas? There are two aspects to this: a. Each cell can be locked or unlocked - this is controlled through Format | Cells | Protection tab, and the default setting is Locked. b. The worksheet can be protected (and this can have a ...

Using a MIN, MAX formula on a calculated field in a pivot table
How can I create a calculated field that includes formulas with MIN, MAX or AVERAGE? Becasue the summary form calculated field of a pivot table cannot be changed (is always SUM), Excel returns a different number when I try to insert a field that includes such a formula. I am trying to include a field like this to calculate minimum possible cost: =MIN('Price')*SUM(Order Quantity) Any suggestion on how to do it? ...

Multiple Payslips
I have a report that prints out payslips, one to the page. There is however room for at least two payslips per page. I have searched for info on how to print multiple payslips per page without success. Any asistance would be greatly appreciated. Thanks There is only one record per payslip. The query that is the datasource returns a record for each payslip for every employee. This is an old database using access 97 "Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message news:DB34A754-1E1E-4477-AE59-FD32B94C843B@microsoft.com... > Are there multiple records per p...

Importing from multiple Excel files
Need to save time, I think this must be possible. I have to constantly pull data from certain cel's, which are contained in any one of our more than 8000 files. I hope this doesn't sound confusing, but: Is there a way to import data from lets say cel E9, from thousands of files, and have them all imported into a new excel workbook? Any advice would be appreciated, Ryan Hi Look on my site for example code http://www.rondebruin.nl/tips.htm Maybe Ado is a option for you http://www.rondebruin.nl/ado.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Tired of wasting...