Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-02-2017, 07:49 PM
jassie jassie is offline excel 2013 summary line totals float Windows 7 64bit excel 2013 summary line totals float Office 2007
Novice
excel 2013 summary line totals float
 
Join Date: Feb 2012
Posts: 18
jassie is on a distinguished road
Default excel 2013 summary line totals float


In excel 2013, I have 5 columns where there needs to be a summary of the amounts
in that column. The requirement is that the summary amounts always have to be
the final value of that column. The summary line
for all 5 rows will always be the same. The problem is detail row lines may be
deleted and/or inserted. This makes the final summary be able to move.

Due to this circumstance, is there a way to allow the summary line to:
a. always display the correct values, and
b. move in a worksheet that is protected?

If so how would I accomplish this goal? If not. do I have any alternatives
and/or workarounds and what would those alternatives are workarounds be?
Reply With Quote
  #2  
Old 03-02-2017, 10:15 PM
xor xor is offline excel 2013 summary line totals float Windows 10 excel 2013 summary line totals float Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

(1) Convert your range to an Excel Table, right click somewhere in the table and press Table, then press Totals Row.
(2) Use a formula like: =SUM($A$1:UpOne) where UpOne is a named formula entered in A2 and the formula should be: =A1 (without any dollar signs)
(3) Use a formula like: =SUM($A$1:INDEX(A:A, ROW()-1)) or
(4) File, Options, Advanced, tick Extend data range formats and formulas.
Reply With Quote
  #3  
Old 03-03-2017, 07:59 AM
jassie jassie is offline excel 2013 summary line totals float Windows 7 64bit excel 2013 summary line totals float Office 2007
Novice
excel 2013 summary line totals float
 
Join Date: Feb 2012
Posts: 18
jassie is on a distinguished road
Default excel 2013 summary lines

Where will the total lines appear? At the bottom of the 5 summary columns?
Reply With Quote
  #4  
Old 03-03-2017, 08:07 AM
xor xor is offline excel 2013 summary line totals float Windows 10 excel 2013 summary line totals float Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Can you upload a file that shows your problem?
Reply With Quote
  #5  
Old 03-03-2017, 08:12 PM
jassie jassie is offline excel 2013 summary line totals float Windows 7 64bit excel 2013 summary line totals float Office 2007
Novice
excel 2013 summary line totals float
 
Join Date: Feb 2012
Posts: 18
jassie is on a distinguished road
Default

I can not load an excel file since there is 'private' data that would be displayed.
The excel document looks basically like

column 1 column 2 column 3 column 4
10 15 20 25
10 15 20 30
5 10 15 15
totals 25 40 55 70
Reply With Quote
  #6  
Old 03-03-2017, 11:01 PM
xor xor is offline excel 2013 summary line totals float Windows 10 excel 2013 summary line totals float Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Take a look here.
Attached Files
File Type: xlsx Sum_UpOne.xlsx (16.5 KB, 11 views)
Reply With Quote
  #7  
Old 03-04-2017, 05:14 PM
jassie jassie is offline excel 2013 summary line totals float Windows 7 64bit excel 2013 summary line totals float Office 2007
Novice
excel 2013 summary line totals float
 
Join Date: Feb 2012
Posts: 18
jassie is on a distinguished road
Default

Would you tell me what I am looking for in the file?
Reply With Quote
  #8  
Old 03-04-2017, 09:53 PM
xor xor is offline excel 2013 summary line totals float Windows 10 excel 2013 summary line totals float Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Based on what you wrote in #1:

The problem is detail row lines may be
deleted and/or inserted.

and

is there a way to allow the summary line to:
a. always display the correct values, and

I thought that you could use one of the examples in the uploaded file. The three examples correspond to (1), (2) and (3) mentioned in #2.

If none of those proposals are of any help to you, please explain better.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
excel 2013 summary line totals float Total float in GANTT incorrect for summary task projman Project 2 02-24-2017 08:54 AM
excel 2013 summary line totals float Negative total float loukas Project 2 11-29-2015 10:38 AM
Resource Assignment Summary Line Not Adding Up Correctly PeteS Project 2 07-18-2014 06:24 AM
Rollup Summary Tasks to Project Summary Task shep555 Project 1 05-24-2014 12:28 PM
Can you either (a) float a page of different orientation or (b) rotate table? New Daddy Word 9 11-14-2013 01:55 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:24 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft