repost; Automatically updating formulae in multiple sheets

I list sales of 20+ products each week for the year
A2-A23 are the products and B-BA are the colums for each week
I have multiple sheets that have the same sales results in the same format 
foreach previous year 2004;2003;2002;2001;2000
Each week I want to compare total sales in the year to date i.e weeks 1-20 
with weeks 1-20 in each previous year. so next week it will be 1-21.
I have on each sheet a cumulative total for each of the 20 products in the 
year to date i.e  Cell A25=SUMB2:BA2 and Cell A26=SUMB3:BA3 and so on for 
each product. This means each week I have to alter the formula in Cell A25 to 
the right number of weeks and then copy& paste down for all 20 products. On 
each of the previous year sheets I have to do the same. Is there a 
formula/instruction I can put into Cell A25 in the 2004 sheet  that will 
change its reference to the same range of cells as I have put into the 2005 
sheet. Can I then cascade that down to the sheets for 2003, 2002 etc? If this 
possible it would save me a lot of work each week.
0
12/22/2005 3:25:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
520 Views

Similar Articles

[PageSpeed] 46

Send me the workbook and this information you wrote here. Also more info as 
you think of it.

excelmarksway@yahoo.com.au

No guarantees of success though...

http://www.geocities.com/excelmarksway



"Keith Nicholls" wrote:

> I list sales of 20+ products each week for the year
> A2-A23 are the products and B-BA are the colums for each week
> I have multiple sheets that have the same sales results in the same format 
> foreach previous year 2004;2003;2002;2001;2000
> Each week I want to compare total sales in the year to date i.e weeks 1-20 
> with weeks 1-20 in each previous year. so next week it will be 1-21.
> I have on each sheet a cumulative total for each of the 20 products in the 
> year to date i.e  Cell A25=SUMB2:BA2 and Cell A26=SUMB3:BA3 and so on for 
> each product. This means each week I have to alter the formula in Cell A25 to 
> the right number of weeks and then copy& paste down for all 20 products. On 
> each of the previous year sheets I have to do the same. Is there a 
> formula/instruction I can put into Cell A25 in the 2004 sheet  that will 
> change its reference to the same range of cells as I have put into the 2005 
> sheet. Can I then cascade that down to the sheets for 2003, 2002 etc? If this 
> possible it would save me a lot of work each week.
0
1/5/2006 3:39:02 AM
Reply:

Similar Artilces:

Filtering no longer works post updates................
After installing Office 2003 SP1 [Office2003SP1-kb842532-client-enu] and the latest Outlook Junk E-Mail update [Update for Outlook 2003: Junk E-mail Filter (KB870765)] my filtering no longer works even if redone. My ISP tags spam with -- Spam -- and I had a rule thought I remade it to try to get it to work that deleted all email with spam, Spam, and -- Spam -- in the subject line. I tired just spam, Spam, and -- Spam -- alone and that did not work either. All mail junk, not junk but new, and -- Spam -- go to junk email folder which is not how I want it or how it was working before. It w...

Look at important update from Microsoft Corp.
--zdeovzzsxbtkgw Content-Type: multipart/related; boundary="ulqslblmzedfjbu"; type="multipart/alternative" --ulqslblmzedfjbu Content-Type: multipart/alternative; boundary="ebeetjgkwpwmqrx" --ebeetjgkwpwmqrx Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Client this is the latest version of security update, the "November 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to...

update stock prices
I have about 200 csv files in a directory. For example, C:\myfolder\amd.csx, C:\myfoler\bux.csx, C:\myfolder\abc.csx .... The csv files have the following format: AMD, 12-Apr-00, 1.4, 1.3, 1.22, 1.5, 627044 AMD, 13-Apr-00, 1.33, 1.23, 1.45, 1.33, 667788 AMD..etc.. First value is the same as the file name, second value is a date (dd-mmm-yy), the rest are numbers. Now I have a .txt file. The format is: AMD, 091012, 1.88, 1.67, 1.45, 1.23, 345678 BUX, 091012, 11, 11.35, 10.9, 11.2, 627044 ABC, 091012, 10.86, 10.89, 10.75, 10.75, 476009 etc. The first value is the name...

Coding a form to update a table
I have a table in my database that needs to have pricing updated, on a weekly basis. I get an update in an excel sheet that is emailed to me. Is there a way that I can program a button on a menu form, in my database, to follow a path satement and find this excel spreadsheet and then over-write the table that needs to be updated. I'm using access 2007. Can someone give me the vba commands to use to write this action, if it is possible. Thanks. -- Don Rountree lookup the TransferSpreadsheet command in Access HELP. -Dorian "Don" wrote: > I have a table in my databa...

Automatic Setup of Custom Menu in Toolbar
I'm trying to create a custom menu in the toolbar. Since this menu is local to my machine, I tried to record a macro that will reproduce it for anyone using the file. But that didn't work. I got a ton of blank menus & then a debug error. Is there any way to "carry over" the custom menu to anyone using the file instead of me having to manually setup the menu on each person's machine? --- Message posted from http://www.ExcelForum.com/ hail Instructions and code for creating/deleting custom menu items. http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q16675...

repost; Automatically updating formulae in multiple sheets
I list sales of 20+ products each week for the year A2-A23 are the products and B-BA are the colums for each week I have multiple sheets that have the same sales results in the same format foreach previous year 2004;2003;2002;2001;2000 Each week I want to compare total sales in the year to date i.e weeks 1-20 with weeks 1-20 in each previous year. so next week it will be 1-21. I have on each sheet a cumulative total for each of the 20 products in the year to date i.e Cell A25=SUMB2:BA2 and Cell A26=SUMB3:BA3 and so on for each product. This means each week I have to alter the formula in C...

Formula #3
YE2002 1Q03 2Q03 3Q03 4Q03 Matt 5 6 7 8 9 Steve 2 3 4 5 7 John 4 5 6 7 6 Fred 1 2 3 4 5 Jane 7 6 5 4 4 Joe 6 5 4 3 3 NEXT TAB Recent Qtr 2Q03 Steve John Jane What formula can I use so that the recent qtr column gets filled in? Then when I change the Recent Qtr to 3Q03, it automatically updates with those numbers. =VLOOKUP(A3,Sheet1!A$2:F$7,MATCH(B$2,Sheet1!A$1:F$1,0),FALSE) -- Mike Ref to "Matthew" <matthew_elli...

Repost
I sent his message earlier but have not been able to view it from here so I apologize if it has already been answered. I had heard that the refresh was released sometime last week but have received no notification from MS about it. When will I know? Will MS send an email to everyone that purchased the Beta 2 Kit? I have clicked "Check for Updates" but there is no information there. All I get is "All your office products are up to date". Thanks for reading. Hi Robin, I have the following URL to view, nothing more. http://officebeta.microsoft.com/home/default.aspx I rea...

How can I save an attachment to process it automatically?
I receive a data file via e-mail each night. I want to have it automatically saved so my databse system can import it and process it automatically before anyone arrives at work. There are some third party addins listed at http://www.slipstick.com/addins/housekeeping.htm that may be able to do what you want. There may be some additional addins listed on other Slipstick pages -- do a search for "attachment". -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** ...

automatic send/receive #2
I already posted this request yesterday (in answer of yours), but not only I have not received a new answer, but I can't find my message anymore. I can't send/receive automatically. Outlook 2000, Win 98 SEThe MVP told me to go to tools -> option -> click automatic receive, but I don't have this option available. A friend of mine just checked my computer, he too uses OUtlook 2000, and he has it. So I am wondering if there is a bug in my program, and what I should do. Thanks What you should do is post your mail support mode, mail account type and connection type, so som...

reading data from hidden sheet
I have a macro to copy and past data from one sheet to another. It works fine when I have all the sheets open, but when I hide the source sheet (where my raw data is stored) macro gives me an error (400). I want my raw data sheet hidden, what can I do to keep my macro working when raw data sheet is hidden? Please advise.... Thanks! As ALWAYS, post your code for comments. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Atiq" <Atiq@discussions.microsoft.com> wrote in message news:FCC02AA8-B4D9-4202-AF00-6384AB099FC9@microsoft.com...

Updating Status Bar,
I have 4 views and I have a status bar pane that outputs the mouse position coordinates relative to the view the mouse lies in. I added a command user interface update handler (in the view class) to update the status bar with the latest coordinates. However, it only works (i.e., updates the coordinates in real time) for one of the view windows until I click on a different one (then it works for the view I clicked but no others). How can I get it to always work whenever the mouse lies inside a view window? That's right. That's how it is supposed to work. The update handle...

auto numbering/sheet of cards
Have to make some cards up. I need to do several on a sheet, when I set it up to do more than one card, how do you do it so it numbers them, say starting aat an odd number, then in numerical order from there? Build a database with consecutive numbers and use mail merge. Help here: http://office.microsoft.com/en-us/publisher/CH062524751033.aspx You can use Excel, Access, a Word table or Publisher to build your list. There are other databases. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Bruce" <notreally@idontw...

Automatic selection
I have a price list and want to set something up, so that when I type a code of a product in, it will automatically bring up the price. So for example I type in A1234, I want it to automatically bring up the price from price list on another sheet (the sheet is in the same workbook). It doesn't matter if the price shows in the same cell or a different cell. Is there anyway that I can do this? Bonny, If I understand what you wish to do, I believe that you can do it by first setting up a table of the product codes with their prices, in a separate part of the worksheet, or more safely on...

Help with a formula
I'm trying to create a formula to do the following, can someone please help?? I want to take the sum of B25 & B26 and divide that sum by the sum of B40 & B41 divided by 2. On Mon, 11 Jan 2010 09:23:01 -0800, mom2hallie <mom2hallie@discussions.microsoft.com> wrote: >I'm trying to create a formula to do the following, can someone please help?? > >I want to take the sum of B25 & B26 and divide that sum by the sum of B40 & >B41 divided by 2. Try one of these, depending on if it is the sum of B40 and B41 that should be divided by 2 or i...

Had to repost it!!!
Query: Say i have multiple sheets dated 04/01/10 to 04/30/10, each contain names, say A, B, C, D, etc... but these name does not appear in the same order in each sheet. Now data is in time format in front of A, B, C, D, etc. Eg. in sheet 04/01/10 A1 B1 A 1:00:00 D 1:53:00 C 2:45:00 like this data in other sheet till 04/30/10. Now in a new sheet i want this data to be collated in time format in front of the individual sorted in Alphabetical order. Also it should give me the Sum of the data. Is it possible and how? Thanks in advance for the help!...

receiving multiple copies of emails
I have Outlook 2002 SP2 and I'm receivig multiple copies of emails (between 2 and 10 copies of the same message). My POP3 mail server administrator says that my Outlook is damaged and is not sending receipt acknowledgements to the server so that the server never deletes the sent messages and continues send them over and over again. How can I fix this porblem? Please respond to adddress listed. Many thanks I have a client that is having the same issue and I have been troubleshooting it for 2 days and can't find anything on microsoft's website. Have you done a windows upd...

Excel Mathematical formula
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I am trying to put into an Excel Spreadsheet, (a fairly basic formula, but I am having a tough time figuring it out. I could use some guidance on the subject. The Formulas is as follows: <br><br>X times 120/113 Asterisk :-) X*(120/113) You need the brackets to force the division to occur before the multiplication. If X is 10, the answer is 10.61946903 Cheers On 6/05/10 3:13 PM, in article 59bb80b3.-1@webcrossing.JaKIaxP2ac0, "MFSeifer@officeformac.com" <MFSeifer@officeformac.co...

Excel Formula: Text equivalent to MODE?
Is there a way to reflect the most commonly occurring text string in non-contiguous array? For example, Cells A1:A6 all contain the string ABC Cells A7:A10 all contain DEF I want cell B1 to reflect the most commonly occurring value (MODE) ou of cells A1, A3, A5, A7, and A9 (ABC). Can this be done without using VBS? -J -- Message posted from http://www.ExcelForum.com JJ, Here's one worksheet formula method, that requires a couple of helper columns, so your final formula is in cell D1, not cell B1. The ranges used are based on your example: In cell B1, use the formula =A1 In cell...

How to convert Grid to Chart with Marcos Automatically?
How to convert from Grid to Chart/Graph with Marcos Automatically? I appreciate your help.... Thanks in Advance -- mindless ------------------------------------------------------------------------ mindless's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26112 View this thread: http://www.excelforum.com/showthread.php?threadid=394383 You mean convert values in the worksheet into a chart? Turn on the macro recorder and carry out the steps you want automated. With questions about customizing the macro, post back with specific questions. - Jon ------- Jon ...

Repost
My question may have something to do with this. I have 6 email accounts set up in Outlook 2003 (as I did in Outlook 2002). Only one (pop) is set to automatically send and receive, but Hotmail (only one http) sends and receives ANYWAY, even though in mail settings, there is a clear "red X" on Hotmail. I guess the beauty of 2003 is that the ongoing problem I had with 2003 on two computers with mulitple Outlooks running seems to be gone. ...

Non-updating formulae
Hi, I'm having trouble with a spreadsheet. I copied the spreadsheet fro another user (not sure of Excel version) and loaded it onto my pc. There are a lot of formulae within this spreadsheet, and i've no discovered that when i change values which should update the formul output, there is no change. It is as though the original output valu is frozen in the cell. If I retype the formula, it works. However, there are so many formula this is not a realistic solution. And if i retype the formula and the 'fill', the new number is shown in all the cells, regardless of whethe th...

REPOST:
I am able to post to newsgroups, but when I tried to Reply Group to a message I posted (replying to myself) I got a dialog box with three buttons, a check box, and an "I" icon. There is, however, no text in the dialog box, on the checkbox or on the buttons! Clicking any (or all) of the buttons has no apparent affect. The dialog only goes away if I click the "X" in the corner and it never sends my reply. I am using the newsreader built in to Outlook. -Will T You're using Outlook Express, accessed from Outlook. I think it's the initial warning that what you ar...

DATE update, on Save?
The only file, (in Doc Libr), does not have today's date. ? Is there some setting I need to make? ...

sorting data and automatic graphs
i have a large amount of data that is an xy scatter. it is xy data that pertains to many subjects. i need to make a graph for each subject. as of now i make the first one and then cut and paste to a new one, use source data and change the x and y data. this does take me a long time since i have alot of subjects. is there another way to pick my data range and sort by subject to make differenct graphs without cutting/pasting and constantly changes the source data. i tried tinkering with a pivot table but everytime i changed the table, all tables changed. There may be depending on the s...