[mso] Re: Maintaining EXCEL SUM Range
- From: Thomas Hutchins <hutch99999@xxxxxxxxx>
- To: mso@xxxxxxxxxxxxx
- Date: Wed, 17 Jun 2009 11:08:57 -0700 (PDT)
If you have a blank row between the data to be summed and the SUM formula, as
shown in your examples, then include that row in your SUM range. New rows added
within the SUM range will automatically cause the range to expand. In your
example, you added a new row outside of the SUM range. For example, if your
original SUM formula had been =SUM(B2:B4), and you inserted a new row 4
(pushing the old row 4 to row 5, and the SUM cell to row 6), the formula would
adjust to become =SUM(B2:B5). Since blank cells have a value of zero, the SUM
results won't be affected by including the blank row.
Hope this helps,
Hutch
--- On Wed, 6/17/09, larryspcremedies@xxxxxxx <larryspcremedies@xxxxxxx> wrote:
From: larryspcremedies@xxxxxxx <larryspcremedies@xxxxxxx>
Subject: [mso] Maintaining EXCEL SUM Range
To: mso@xxxxxxxxxxxxx
Date: Wednesday, June 17, 2009, 11:03 AM
I need some help in addressing a problem I'm having dealing with inserting a
row of data in a worksheet and insuring that the range in the SUM(..) functions
in a final total line are properly updated.
The worksheet has rows in it that reference other sheets in the workbook, I
call it the "Summary" sheet. As worksheets are added to the workbook, I need to
insert a row in the Summary sheet pushing the final total line down.
The problem I'm having is the ranges in the SUM(..) functions are not updated
to reflect the new row.
E.G.
--- Initial
? Sheet
1 Name??????? Balance
2 Tory????????? 5? <-- =INDIRECT(A2 & "!J22")
3 Valance?????? 9? <-- =INDIRECT(A3 & "!J22")
4
5 Total??????? 14? <-- =SUM(B2:B3)
After insertion
? Sheet
1 Name??????? Balance
2 Tory????????? 5? <-- =INDIRECT(A2 & "!J22")
3 Valance?????? 9? <-- =INDIRECT(A3 & "!J22")
4 Door????????? 6? <-- =INDIRECT(A4 & "!J22")
5
6 Total??????? 14? <-- =SUM(B2:B3) but expect =SUM(B2:B4) expecting a balance
column total of 20
Now, this example is a simple reflection of the actual sheet that contains
numerous column totals. Further, I would like to run a macro that actually
inserts and duplicates the data rows, which I actually have completed ... it's
maintaining the darn SUM ranges that are kicking me in the shins.
Anybody have any ideas on how to address this problem?
Larry
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the dropdown
menu at the top. This will allow you to unsubscribe your email address or
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to share files with the group, you must join our Yahoo sister
group. This group will not allow for posting of emails, but will allow you to
join and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.
If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the dropdown
menu at the top. This will allow you to unsubscribe your email address or
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to share files with the group, you must join our Yahoo sister group.
This group will not allow for posting of emails, but will allow you to join
and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.
If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
Other related posts:
