Formula "Change" Problem

Situation:

In cell A1, I have a simple formula that sums the data in the following
6 horizontal cells.

The formula is =SUM(B1:G1)

I want this formula to never change under any circumstance.

The problem is if I insert a column before in front of column B the
formula changes to the following:

=SUM(C1:H1)

The spreadsheet is setup so that it is supposed to calculate a rolling
6 week total. Each new week a new column will be inserted and the data
for the new week entered.

How can i keep the formula from changing as the worksheet changes?

Thank you in adnvance to anyone that may be able to help.
--
mholc3


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

0
1/23/2004 1:19:47 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
494 Views

Similar Articles

[PageSpeed] 25

The following should work for you:

=SUM(INDIRECT("B1:G1"))

-- 
HTH,
Laura Cook
Appleton, WI


"mholc3 >" <<mholc3.10gnex@excelforum-nospam.com> wrote in message
news:mholc3.10gnex@excelforum-nospam.com...
> Situation:
>
> In cell A1, I have a simple formula that sums the data in the following
> 6 horizontal cells.
>
> The formula is =SUM(B1:G1)
>
> I want this formula to never change under any circumstance.
>
> The problem is if I insert a column before in front of column B the
> formula changes to the following:
>
> =SUM(C1:H1)
>
> The spreadsheet is setup so that it is supposed to calculate a rolling
> 6 week total. Each new week a new column will be inserted and the data
> for the new week entered.
>
> How can i keep the formula from changing as the worksheet changes?
>
> Thank you in adnvance to anyone that may be able to help.
> --
> mholc3
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
lwilde (28)
1/23/2004 1:31:12 AM
Hi,

Use INDIRECT function to keep a reference unchangeable, like this:

=SUM(INDIRECT("B1:G1"))

Problem in your example is that if you insert a column before this
formula, then it would create a circular reference (hence the function
is in B1 and B1 is part of the sum area)

- Asser


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

0
1/23/2004 9:46:29 AM
Reply:

Similar Artilces:

Perhaps not an outlook problem, but maybe a setting change will help.
Hello, I'm using Outlook 2003 on a Vista Business system. I use it to monitor four email accounts, one POP mail from my work, a gmail account, and two POP accounts from my ISP (Bell), who contracted their mail service to Microsoft some years ago. The two non-ISP accounts work perfectly and are very reliable, but intermittently, one or the other of the ISP accounts start rejecting the username and password, which I know is correct. If I go to the web mail interface and test the username and password - there's no problem, unless the login server really, really is broken, w...

How do I change start-up default settings?
I want my Publisher 2003 to open with different 'arrange', font & font size settings to those originally set by Microsoft A tutorial Setting Text Defaults using Normal.pub by Brian Kvalhei http://ed.mvps.org/Static.aspx?=Publisher/normal.pub -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "DISMAL" <DISMAL@discussions.microsoft.com> wrote in message news:3718CDE5-4970-4A4B-B17F-AFBAAA40E60E@microsoft.com... >I want my Publisher 2003 to open with different 'arrange', font & font size > set...

Change Junk E-mail SCL
I found out one day that you could change the SCL of what messages will be moved to Junk E-mail folder in Outlook. Does anyone know how this is done and also what is the default SCL level that is filtered to junk E-mail folder? GC GauiC <gudjonmar@gmail.com> wrote: > I found out one day that you could change the SCL of what messages > will be moved to Junk E-mail folder in Outlook. Who told you that was possible? > Does anyone know how this is done and also what is the default SCL > level that is filtered to junk E-mail folder? As far as I can tell, there is no &quo...

Change the default color Excel 2007 uses to highlight selected cel
I'm using Excel 2007 and I'd like to change the default color Excel 2007 uses to highlight the selected cells in a worksheet. When selecting a range (ex. A1:D10). The selected range takes on a light transparent blue. Very hard to see when working in selected range. I've tried changing Office>Excel Options>Popular>Color Scheme - 3 colors to choose from (Blue, Silver, Black). This doesn't make a difference with the selection color at all. Also tried to change the Personalization>Appearance>Different Color Schemes with the Advanced option>Item areas, thi...

Change default sort order
Every object in my Navigation Pane is sorted 'Ascending.' This seems to be the default setting. I want to sort 'Descending' (on date created or modified) in order to bring the most recent object to the top of the Navigation Pane for tables, forms, queries and reports. I can't find anywhere in Access Option a place to make this change. Is this possible? How? VBA code? Ed This is a report forum so I can only assume you want to set the sorting of records in a report. Do this using the Sorting and Grouping dialog. -- Duane Hookom Microsoft Access MVP "OldManEd&...

Is there a way to change preset margins?
I have figured out how to change the default template, which is great. Is there a way to change the margin presets in Word 2007? I don't want to change the default/normal template - I use several types of page marins routinely, but only a couple of them are in the presets. However, I NEVER use the 2003 Default margin with 1.25" side margins. Is there a way to create my own presets to those that I use regularly? Thanks No way to create presets, but you can certainly create templates with the desired margins; see http://word.mvps.org/FAQs/Customization/CreateATemp...

How do I change the category axis number formatting in a Pivot Ch.
I have created a Pivot Chart from Excel data (Excel 2000) and I need to figure out how to change the category axis number formatting. Currently, the dates in the chart are showing up in long form (01/01/2005) and I would like to change this to something shorter like 1/01. I changed the formatting of the data in the original raw data and then in the Pivot Table, but neither of these seemed to change the Pivot Chart formatting. When I right click on the category axis, I do not get the option the change the number formatting (I do for the value axis however). ...

Language changes after update query
I have a data entry form where the user fills out text, numerical data, dates, etc. I created a button in the same form that runs a macro in background containing several update queries. At least 1 record changes from English to some other kind of language or font but it looks like Japanese or Chinese characters. I can't figure out why this happens and only on the that 1 macro. I would very much appreciate your input. TIA On Wed, 5 Mar 2008 10:10:01 -0800, Y2 <Y2@discussions.microsoft.com> wrote: >I have a data entry form where the user fills out text, numerical data, ...

Personal Address book address will not change/update
Hello, I have Exchange 2000 and Outlook XP/2002. I have a personal address book. When I attempt to update the email address of an entry, it always returns to the original SMTP address. Is this a bug? Has anyone encountered this before? Thanks, Rich ...

Switching to a shared Calendar in a Public Folder changes the input locale?
Hello, We have the Outlook client installed in English everywhere but many users have keyboards with layouts for different languages. When some users go to certain public folders and open, e.g., a shared calendar, the language bar changes from EN to another layout. It's a soon as they click on the Calendar tree - they don't even have to open an item. Does the top-level folder take the layout of the creator? How can it be changed, and avoided in future please? Thanks, - Alan. ...

Cannot see changes made to leads fields
I added additional industry in the industry dropdown in leads. I did this through the web interface lead customization section. I can see the change when I say preview. But when I go to add a lead I do not see the changes. I stopped and started the services still no odiffrence. I can still see it in preview but not when I try to add a lead. -SKP Publish the customization using the deployment manager and then restart IIS (iisreset) Stephen "Supertycoon" <supertycoon@gmail.com> wrote in message news:754fd6bc.0411101450.3c535e84@posting.google.com... >I added addition...

keep changes are made on datasource.
Hello. I have DataTable object. Changes are made on the datatable, but I need sometimes to refresh the datatable contents (I have master-detail form, and when the master record is changed, there are new elements on the datatable). So, I need to keep changes that were made on the details table. How can I keep the old changes, before any commit to database ? Need an example, please. Thanks :) Sorry. Problem solved ! I have to use the same dataAdapter from the time I initialize the dataset through its whole lifetime. Thanks :) ...

Field History, DB Change audit.
I want a way to register a history item when someone changes a field from one thing to another. Is there a way to do that? You could export the system an an xml file. Later on if someone has made a change you can export to xml again and compare the differences. Not nice but it will work "Jake Horn" <nospam@nospam.com> wrote in message news:%234Cai6c6DHA.1428@TK2MSFTNGP12.phx.gbl... > I want a way to register a history item when someone changes a field from > one thing to another. Is there a way to do that? > > What I mean is... If someone changes the assig...

change default file open position for large shared spreadhseet
We have a large multi-sheet shared file that always opens to the second sheet with the entire sheet highlighted. Have to constantly switch back to the first sheet. How do I stop Excel from defaulting to a specific sheet? Or better yet, how do I set which sheet opens first and where it starts? Excel remembers the selection (and sheet) where it was last saved. So you could select the sheet/range that you want before you save the workbook. Or you could have a macro run when excel opens the file and do it for you: This would go in a General module: Option Explicit Sub Au...

How do I change the shape of a box in my visio (square to circle)
I have a completed flow chart in Visio but I want to use different shapes. How do I select several shapes at once and convert them to a different shape? Hi Michael, There's no real "Shape replace" feature in Visio. The connectors in Visio 2003 are pretty good at recognizing that a new shape is being inserted if you delete shapes, then drag new ones in to place. In the flowchart stencil, there is a "Flowchart shapes" shape that allows you to switch between Data, Decision, Output and Process by simply right-clicking the shape. Beyond that, there are third party s...

How to change the value inside Legend fields?
Everytime I print the Reprint Statements from Tools > Routines > Sales the "**Reprint**" text will printed at the report header. How can I change this text to some other text like "Repeted Document" ? I knew this text is came from "Legend[1]" field. How can I modify the value in Legend fields? Please Advise. Thanks. Although you cannot change the legend, you can use a calculated field in RW - if legend1 = "**Reprint**" then "anything you want" I usually just remove or hide that field HS ----------------------------------------...

programatically change chart
I am using powerpoint to programatically create slides displaying MS GRaph Charts. I am using .NET and C#. I am having trouble location help for this topic. My questions are: 1. I want to programatically change the direction of the Category Axis. I wish to rotate them so they are upright instead of on their side. How can I do this? objCatAxis.TickLabels.Orientation = Graph.XlTicklabelOrientationUpWard does not seem to be working. 2. I am having trouble defining colors for my charts. It seems to be happening automatically. I want to control which colors get displayed. I wish to do this pro...

New document defalt is center how do I change to default left?
Recently Wood 2007 changed on me. Everytime I open a new document the cursor is in center position. This creates a problem with I want to print a label as it is centered. How do I change this default back to the original default of "left" cursor position? In Word, create a new, blank document. Click the Page Layout tab on the ribbon. In the Page Setup group, click the dialog launcher button (the little arrow on the bottom right). On the Layout tab, make sure that Vertical alignment is set to "Top." Click the Default button. If/when you are prompted to sav...

Change the subject of a massage
Hi, Can I change the subject of a massage without opening it? thanks []'s No. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Mauro asked: | Hi, | | Can I change the subject of a massage without opening it? | | thanks | []'s Go to View > Current View > Customize Current View > Other Settings > check "Allow in-cell editing". Not all...

Compare and merge not showing changes to footer
I often need to show the change in a footer from a version 1 of a document to a version 2 (using NetDocuments online depository in our office). Many times the changes do not show in the footer, like the document number would be 12345v1 and the new version 12345v2, and a compare and merge will not show any change. This is a significant problem, as clients need to see that they are different documents. Help please! ...

Change driver of network printer on local machine?
We have a bunch of computers in the office which connect to a Win2003SBS. We have two Brother laser printers on our network. Is it possible for my computer to use a different printer driver for one of the printers or would I need our network consultant to login to the server and change the driver? The reason I ask is because there's two types of drivers.. PCL and BR-SCRIPT. The PCL offers more features. I'd like to start taking advantage of them. One of the features is a macro feature, which can store stuff in the printer memory, such as a letterhead you use often...

exchange 5.5/outlook 2000 problem
I have an Exchange 5.5 with Outlook 2000. The problem is that one of the mailboxes receives a request for a meeting but doesn’t always apply this to its calendar and never reply with an acceptance etc. In the equation the one reply received was late i.e. sent meeting invitation at 4:30pm received acceptance at 9:00 following day. Could this problem be related to the exchange, outlook or other area? ...

Yesterday's Prices, Today's Price Changes
When I update prices of mutual funds, I get yesterday's prices listed but with price changes from today. So, my Net Worth calculations are always a day behind (not a bad thing when the market drops.) Yet when I update stock prices, everything works as it should. Any suggestions? Using Money 2004 under Win 98. Are you updating online ? Is the clock on your PC correct ? "Blue Yonder" <dontbotherme@nospam.com> wrote in message news:120hkjl4tb88u8f@corp.supernews.com... > When I update prices of mutual funds, I get yesterday's prices listed > but with price c...

Outlook / Exchange 2003 user connection problem
Ok, we are doing our Outlook configuration in a two step process first we add the Computer to the domain ( not the user ). Then we configure Outlook with the exchange server and user information. All of our users are created and have a default password. Problem is that when we go to setup Outlook we are only able to setup 2 - 5 computers a day. After we get 2 - 5 setup, when we try to install/configue another Outlook client we receive a message that say Cannot connect to Exchange server or Exchange server does not exist. But if we wait a day and go back to setting up Outlook again we are ab...

display on first occurrence of a change in the value
How can I have a report display a text message upon the first occurence of a change in the value of a field in the underlying table? In other words, the field in this table has the same value for many records in a row, but all of a sudden that can change and I want to alert the report reader once that subsequent records are now reflecting these changed values. What I think I need is some kind of switch that changes from say 0 to 1 when that first change happens. Thanks. Alan adgon, Use the Hide Duplicates propety set to Yes. Only the first instance of each value will display. --...