#### Formula - relative or absolute ref, keeps changing

Have a simple idea that is giving me fits.

Have a worksheet with numberic data and labels in rows and columns.  Below
each column I use a simple SUM() function which works fine, until I disturb
a row of data.  By disturb, what I mean is that if I move the top row of
data down to the bottom of the array, highlight the entire data area, move
that area up one row, I lose the correct references in the SUM() function.
I'm just moving the data area, not the row containing my SUM() functions -
that stays put.  IOW, the SUM() function is in row 12, the data area extends
from row 1 through row 10, I move row 1 to row 11 and then move everything
from row 2 through row 11, back up one row.

I've tried using relative and absolute addressing but that SUM() formula
somehow keeps adjusting based on what it sees me moving and giving me an
incorrect result.  How do I make the formula "static", or to just tell it
"sum the numbers above and don't watch what's moving around, dog-gone it!".

 0
whftherb (5)
7/22/2005 1:25:09 AM
excel.newusers 15348 articles. 2 followers.

5 Replies
706 Views

Similar Articles

[PageSpeed] 59

To use absolute addressing try =SUM(\$A\$1:\$A\$10)

"Hoib" wrote:

> Have a simple idea that is giving me fits.
>
> Have a worksheet with numberic data and labels in rows and columns.  Below
> each column I use a simple SUM() function which works fine, until I disturb
> a row of data.  By disturb, what I mean is that if I move the top row of
> data down to the bottom of the array, highlight the entire data area, move
> that area up one row, I lose the correct references in the SUM() function.
> I'm just moving the data area, not the row containing my SUM() functions -
> that stays put.  IOW, the SUM() function is in row 12, the data area extends
> from row 1 through row 10, I move row 1 to row 11 and then move everything
> from row 2 through row 11, back up one row.
>
> I've tried using relative and absolute addressing but that SUM() formula
> somehow keeps adjusting based on what it sees me moving and giving me an
> incorrect result.  How do I make the formula "static", or to just tell it
> "sum the numbers above and don't watch what's moving around, dog-gone it!".
>
> Thanks for any advice here.
>
>
>
>
 0
bigwheel926 (227)
7/22/2005 1:47:01 AM
You can use the Indirect function:  =SUM(INDIRECT("A1:A10"))

Hoib wrote:
> Have a simple idea that is giving me fits.
>
> Have a worksheet with numberic data and labels in rows and columns.  Below
> each column I use a simple SUM() function which works fine, until I disturb
> a row of data.  By disturb, what I mean is that if I move the top row of
> data down to the bottom of the array, highlight the entire data area, move
> that area up one row, I lose the correct references in the SUM() function.
> I'm just moving the data area, not the row containing my SUM() functions -
> that stays put.  IOW, the SUM() function is in row 12, the data area extends
> from row 1 through row 10, I move row 1 to row 11 and then move everything
> from row 2 through row 11, back up one row.
>
> I've tried using relative and absolute addressing but that SUM() formula
> somehow keeps adjusting based on what it sees me moving and giving me an
> incorrect result.  How do I make the formula "static", or to just tell it
> "sum the numbers above and don't watch what's moving around, dog-gone it!".
>
> Thanks for any advice here.
>
>
>

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

 0
dsd1 (5911)
7/22/2005 1:57:45 AM
Thank you bigwheel.  I actually had tried this method too with the same
result.  The expression \$A\$1:\$A\$10 also "flexes" or changes as I move the
top row to the bottom yielding an unwanted result - the moved row is
excluded even though I move the entire 10 row array back into position.  I
will say, however, the INDIRECT() function spec'd in a subsequent post by
Debra, is apparently one answer to this.  I'm also sure there are probably a
hundred different techniques to get this done.  So, again, I thank you for

"bigwheel" <bigwheel@discussions.microsoft.com> wrote in message
news:28E9A2C0-5FDE-4692-9D8F-415D5515FFDA@microsoft.com...
> To use absolute addressing try =SUM(\$A\$1:\$A\$10)
>
> "Hoib" wrote:
>
>> Have a simple idea that is giving me fits.
>>
>> Have a worksheet with numberic data and labels in rows and columns.
>> Below
>> each column I use a simple SUM() function which works fine, until I
>> disturb
>> a row of data.  By disturb, what I mean is that if I move the top row of
>> data down to the bottom of the array, highlight the entire data area,
>> move
>> that area up one row, I lose the correct references in the SUM()
>> function.
>> I'm just moving the data area, not the row containing my SUM()
>> functions -
>> that stays put.  IOW, the SUM() function is in row 12, the data area
>> extends
>> from row 1 through row 10, I move row 1 to row 11 and then move
>> everything
>> from row 2 through row 11, back up one row.
>>
>> I've tried using relative and absolute addressing but that SUM() formula
>> somehow keeps adjusting based on what it sees me moving and giving me an
>> incorrect result.  How do I make the formula "static", or to just tell it
>> "sum the numbers above and don't watch what's moving around, dog-gone
>> it!".
>>
>> Thanks for any advice here.
>>
>>
>>
>>

 0
whftherb (5)
7/22/2005 10:17:17 PM
Perfect!  It works as advertised!  Thanks a lot!

"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
news:42E05299.9040906@contexturesXSPAM.com...
> You can use the Indirect function:  =SUM(INDIRECT("A1:A10"))
>
> Hoib wrote:
>> Have a simple idea that is giving me fits.
>>
>> Have a worksheet with numberic data and labels in rows and columns.
>> Below each column I use a simple SUM() function which works fine, until I
>> disturb a row of data.  By disturb, what I mean is that if I move the top
>> row of data down to the bottom of the array, highlight the entire data
>> area, move that area up one row, I lose the correct references in the
>> SUM() function. I'm just moving the data area, not the row containing my
>> SUM() functions - that stays put.  IOW, the SUM() function is in row 12,
>> the data area extends from row 1 through row 10, I move row 1 to row 11
>> and then move everything from row 2 through row 11, back up one row.
>>
>> I've tried using relative and absolute addressing but that SUM() formula
>> somehow keeps adjusting based on what it sees me moving and giving me an
>> incorrect result.  How do I make the formula "static", or to just tell it
>> "sum the numbers above and don't watch what's moving around, dog-gone
>> it!".
>>
>> Thanks for any advice here.
>>
>>
>>
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>

 0
whftherb (5)
7/22/2005 10:18:27 PM
You're welcome!

Hoib wrote:
> Perfect!  It works as advertised!  Thanks a lot!
>
>
> "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
> news:42E05299.9040906@contexturesXSPAM.com...
>
>>You can use the Indirect function:  =SUM(INDIRECT("A1:A10"))
>>
>>Hoib wrote:
>>
>>>Have a simple idea that is giving me fits.
>>>
>>>Have a worksheet with numberic data and labels in rows and columns.
>>>Below each column I use a simple SUM() function which works fine, until I
>>>disturb a row of data.  By disturb, what I mean is that if I move the top
>>>row of data down to the bottom of the array, highlight the entire data
>>>area, move that area up one row, I lose the correct references in the
>>>SUM() function. I'm just moving the data area, not the row containing my
>>>SUM() functions - that stays put.  IOW, the SUM() function is in row 12,
>>>the data area extends from row 1 through row 10, I move row 1 to row 11
>>>and then move everything from row 2 through row 11, back up one row.
>>>
>>>I've tried using relative and absolute addressing but that SUM() formula
>>>somehow keeps adjusting based on what it sees me moving and giving me an
>>>incorrect result.  How do I make the formula "static", or to just tell it
>>>"sum the numbers above and don't watch what's moving around, dog-gone
>>>it!".

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

 0
dsd1 (5911)
7/23/2005 12:21:52 AM

Similar Artilces:

How to change the string value in the registry?
How to change the "string value" in the registry from the code? As there is a functionality in my application that the user can change the "string value" with some GUI. But I m not able to change it as there is a function RegReplaceKey but its only to take the backup of old file and to replace it with new one and even i dont know how to use this functin to suit my case. RegSetValue/Ex() should do it I think. -Seetharam Look at the Registry APIs. Also, take a look at my Registry class on my MVP Tips site. joe On Sun, 26 Aug 2007 23:59:33 -0700, HItz <hitesh_im...

Name change due to marriage...
I have a user who recently got married and would like to have her new married name for her emails, but also wants external users to be able to send emails to her old address until she has adequate time to inform everyone. Her new email would be something like lastname-newname, firstname instead of lastname, firstname. Should I create a new account with her new name and forward all her emails from the old account to the new one and can I copy her current pst into the new one? THanks for everyone's help. No. Just highlight her account in ADUC, and choose re-name. Make the neede...

Excel Formulae #4
I've been trying hard to find a formula which would enable me (from one cell) to Add several cells and Subtract the answer from another cell (subtracting items of expenditure from a starting total). I could manage this with Lotus - but Excel does not seem to behave the same. Advice would be much appreciated. Thanks, Ken. One way: Say you wanted to subtract A1, A2, A3, J4 and N5 from L6: =L6-SUM(A1:A3,J4,N5) In article <173d01c3fbd6\$3df0f300\$a401280a@phx.gbl>, "KenS." <anonymous@discussions.microsoft.com> wrote: > I've been trying hard ...

Unable to Open received PO (change to "change order")
Great Plains will not allow me to change this Purchase Order from Received to Change Order: Here is the message I get: However, if I try to enter a quantity cancelled, I get: How can I re-open this Purchase Order so I can change the price??? Thank you. Unfortunately, the pasted messages did not come through. Once a line item is received, you can change its cost only during invoice matching. The process would be to return the item; add a new row with the correct cost; and receive and invoice the new item. Not an elegant solution, for sure. -- Charles Allen, MVP &quo...

Tracked changes function misbehaving
I have a simple workbook (2002) in which the Track Changes feature is enabled. End users have reported periodic problems with the contents of cells "mysteriously" changing. In process of investigating these, have found that when tracked changes are highlighted, the comments that describe changes are not always associated with the correct cell. Example, comment on cell B114 states "Changed cell B114 from '<blank>' to '12345'". In fact, contents of cell B114 is "67890" and contents of cell B104 is actually "12345". Has anyone...

Changing font size in data validation drop-down lists
In Excel 2003, I created the value list on a separate worksheet and labled the range so I could use it in the data validation wizard on a different worksheet. The list is working fine; however, the font size in the drop-down list is too small to comfortably read. So far, I have tried: Making the font in the list larger. Formatting larger font size in the active cell with the drop-down button And, creating the list on the same worksheet. All three remedies have not increased the size of the font in the drop-down list. Suggestions are welcome. Great Optimism, Dutch Driver There is n...

Shorter Formula
Can anyone shorten this formula please. Basically all it does is gives me an average of the figures in Column "W" depending on the number of times that product appears in "R" column =IF(ISERROR(SUM(SUMIF(\$R\$5:\$R\$9,R62,\$W\$5:\$W\$9),SUMIF (\$R\$22:\$R\$26,R62,\$W\$22:\$W\$26),SUMIF (\$R\$39:\$R\$43,R62,\$W\$39:\$W\$43))/COUNTIF (\$R\$5:\$R\$43,R62)),0,SUM(SUMIF (\$R\$5:\$R\$9,R62,\$W\$5:\$W\$9),SUMIF (\$R\$22:\$R\$26,R62,\$W\$22:\$W\$26),SUMIF (\$R\$39:\$R\$43,R62,\$W\$39:\$W\$43))/COUNTIF(\$R\$5:\$R\$43,R62)) thanks Pete I didn't try too hard to analyze your formula, just noted that your ranges and sum_ ranges...

Formula counts incorrectly
Help please. Column K contains dates and blank cells. I would like to count how many of these dates fall within a given date range. For example, in the first week in May. Column K contains these dates. 5/2/2005, 5/3/2005, 5/5/2005, 5/5/2005. I would like to count this as 4 dates within the first week of May. I created this formula but the output is 3. =SUMPRODUCT(--(ECNT!F2:F515>=DATE(2005,5,2)),--(ECNT!F2:F515<=DATE(2005,5,6)),--(ISNUMBER(ECNT!K2:K515))) I assume that it is only counting 5/5/2005 one time? I changed the dates in the formula to check a second 2nd date range (5/9 to ...

Excel date changes
I have created a spreadsheet using excel which requires the use dates on a monthly basis (similar to a calendar). The problem I have is that every month I have to go in and change the dates on the spreadsheet to reflect those of the current month before printing. Is there a way I can make it so that excel does that for me every month. Thanks, I'd really appreciate the input. Please elaborate on what the spreadsheet looks like. "phonescoop@hotmail.com" wrote: > I have created a spreadsheet using excel which requires the use dates > on a monthly basis (similar to a cal...

Change order of A/R Statements
How can I change the order in which customer statements print out of RMS? I would like to print statements sorted by company name. Currently, the statements print in some random order....I think based on CustomerID. Paul, I agree. I just had a customer tell me the same thing. They need the statements in Alphabetical order. The current order appears to be straight out of the customer table in the order they were entered. It has been posted as a suggestion - vote for it and push it higher on the "list". Claude "Paul Arenson" wrote: > How can I chang...

Changing URL from Ticker Symbol
Many of partners in Japan want to changing URL from Ticker Symbol in order to jump to a web site in Japan instead of http://moneycentral.msn.com/ . ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowse...

Protect formatting & formula
I need to protect the formatting & formula in each cell to avoid accidental changes during update by different users. However, if I protect them, any new row creation and deletion are disable. Is there any way resolve it? Especially, when create a row, how can all formatting and formula to create automatically for the new row? Thanks, Scott What version of Excel? "Scott" <NoSpam-Scott.Xe@GMail.com> wrote in message news:%23bsg6kCBGHA.4092@TK2MSFTNGP09.phx.gbl... >I need to protect the formatting & formula in each cell to avoid accidental >changes d...

Is there a way to automatically put the \$ sign into the formula?
I want to turn this =A1 into =\$A\$1. How is the best way of doing this. Is there a shortcut....keyboard stroke to do this?? Hit [F4] several times and watch the results. Micky "hrb_2001" wrote: > I want to turn this =A1 into =\$A\$1. > How is the best way of doing this. Is there a shortcut....keyboard stroke to > do this?? > Hi, Enter =A1 then tap F4 Repeated taps of F4 scroll through the referencing options -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently an...

can i see the date the last time a cell was changed?
I am trying to figure out a formulla to make the date appear in one cell everytime anouther cell's data is chaged. Use a worksheet_change event to copy the cell address and put in a date stamp. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "JohnNuTek" <JohnNuTek@discussions.microsoft.com> wrote in message news:5085A544-CBF4-4B81-A244-B03DE9A0E9E6@microsoft.com... >I am trying to figure out a formulla to make the date appear in one cell > everytime anouther cell's data is chaged. There really isn't a worksheet formula to do that. Typicall...

Unhandled database exception: A get/change operation on table 'Bat
During Edit Checks, we get the following message: Unhandled database exception: A get/change operation on table 'Batch_Headers' could not find a record. Then we get this message: This transaction was recovered during normal processing. You may continue processing this transaction. Any suggestions? You might want to run Check Links on payables. Before doing this, make sure you have a backup of your dynamics and company databases. Brenner -- www.KlenzmanConsulting.com "GPI" wrote: > During Edit Checks, we get the following message: > > Unhandled databas...

Help writing a formula
I have a huge database that I need to create numbering for each line like this example: AK-233 (2 letter state, a dash, and 1-4 digit number), but the number doesn't change each line - sometimes the number will be duplicated on multiple lines because those lines have the same title, I also need to create another cell extending that letter/number combo that will differentiate the 1st cell. So the first cell stays the same if the title is the same. The second cell adds another layer of numbering to differentiate the first cell. See example: (Row1, Col 1-4) AK-1, AK-1-1, Title1...

trouble changing data type
I am running MS Access 2007 on Windows XP Professional 2002 (SP2). I need to change the data type of a single field in a table containing just over 1.4M records in order to link it with another table and run a Make-Table Query, but Access is telling me I do not have enough memory to change the data type. I have 1GB of RAM and a Pentium 4, 2.79 GHz processor. Am I out of luck based on the equiptment I am working with, or is there a way around this? Much appreciated. -- Kevin Philadelphia, PA First backup your database. Next Compact and Repair. Third add the new field datatype. C...

Changing size and placement of maximized dialog
Hi, I want to have maximized window not on all screen. how to change maximized size and placement of dialog ? I red doc for SetWindowPlacement(&wPlac) and I have tried in OnInitDialog(): wPLac.length = sizeof(WINDOWPLACEMENT); GetWindowPlacement(&wPlac); wPlac.ptMaxPosition.x = 200; wPlac.ptMaxPosition.y = 200; SetWindowPlacement(&wPlac) But after dialog is maximized it is on all screen. What is wrong ? Peter "Peter": > I want to have maximized window not on all screen. > how to change maximized size and placement of dialog ? > I red doc for SetWindowPlaceme...

keeping messages in inbox
I've been using Outlook for about a month to pull in my emails from a different mail services I have - AOL and SBC/Yahoo. The problem I'm having is that while my messages are coming in from AOL correctly (or they seem to be), Outlook only retains them for a week or so then they disappear. It does not happen with the SBC mail (which is the first/default mail account). I've looked everywhere for something to adjust the length of time messages stay in the Inbox folder for the AOL messages, but no success. Anyone with ideas that might help would be greatly appreciated, be...

How do i keep the colors applied to the specific rows when sortin.
I have applied specific colors to specifc rows, but when i sort the colored rows follow the sorting format. ...

Keeping Custom Properties When Forwarding or Replying to a Message #2
We have emails saved on the file system, that is kept track of by another program. These emails have custom properties attached to them. There is no custom form, just custom properties. These fields were originally added using objPost.UserProperties.Add. We need it so that when someone opens up one of these emails through the file system, and then replies or fowards the message, these custom properties are kept in the reply. This is so the external program can track these emails. Is there a way this can be done? We could have these emails opened up using an Outlook command line that i...