Inserted Rows not re-calculating

I use SUM(Column_Title) a lot as I often do not know how many rows the 
worksheet will grow to.  It works great but when I add in rows, and 
double-click on the formula cell, the extra rows I added are not included in 
the formula range (the colour-highlighted box).

My workaround is to double-click on the formula, then double-click the 
colunm title (in Row 1).  Then when I double-click the formula again, it 
shows it formula range includes the rows I added.

This has been happening for a while, and the workaround is tiresome as there 
are many columns in some of my worksheets (and I have to double-check to make 
sure I didn't miss any).  I have tried F9 and CalculateSheet, both have no 
effect.  Is there some way to get it to update automatically?  (I thought 
that was the point of it, rather than having to input a cell range.)

Excel 2000 in XP.
cheers,
0
klam (18)
8/25/2005 1:50:05 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
556 Views

Similar Articles

[PageSpeed] 8

I find that if I don't include the cell right above my sum cell, that 
happens.  If I include that cell, the additional rows I add are included in 
the total and I don't have to adjust the formula.

"klam" wrote:

> I use SUM(Column_Title) a lot as I often do not know how many rows the 
> worksheet will grow to.  It works great but when I add in rows, and 
> double-click on the formula cell, the extra rows I added are not included in 
> the formula range (the colour-highlighted box).
> 
> My workaround is to double-click on the formula, then double-click the 
> colunm title (in Row 1).  Then when I double-click the formula again, it 
> shows it formula range includes the rows I added.
> 
> This has been happening for a while, and the workaround is tiresome as there 
> are many columns in some of my worksheets (and I have to double-check to make 
> sure I didn't miss any).  I have tried F9 and CalculateSheet, both have no 
> effect.  Is there some way to get it to update automatically?  (I thought 
> that was the point of it, rather than having to input a cell range.)
> 
> Excel 2000 in XP.
> cheers,
0
letto1151 (4)
8/25/2005 2:51:10 PM
Thx for the reply letto115. 

Strange for mine, my Sum (row 57) includes row 56, and when I insert rows 
they are not included in the formula.  

I did a test file to see if I didn't include the row before, if newly 
inserted rows would be included in the formula, and it did not work either. 

Any other ideas?

cheers,

"letto115" wrote:

> I find that if I don't include the cell right above my sum cell, that 
> happens.  If I include that cell, the additional rows I add are included in 
> the total and I don't have to adjust the formula.
> 
> "klam" wrote:
> 
> > I use SUM(Column_Title) a lot as I often do not know how many rows the 
> > worksheet will grow to.  It works great but when I add in rows, and 
> > double-click on the formula cell, the extra rows I added are not included in 
> > the formula range (the colour-highlighted box).
> > 
> > My workaround is to double-click on the formula, then double-click the 
> > colunm title (in Row 1).  Then when I double-click the formula again, it 
> > shows it formula range includes the rows I added.
> > 
> > This has been happening for a while, and the workaround is tiresome as there 
> > are many columns in some of my worksheets (and I have to double-check to make 
> > sure I didn't miss any).  I have tried F9 and CalculateSheet, both have no 
> > effect.  Is there some way to get it to update automatically?  (I thought 
> > that was the point of it, rather than having to input a cell range.)
> > 
> > Excel 2000 in XP.
> > cheers,
0
klam (18)
8/25/2005 4:28:03 PM
Your posting was too ambiguous before but the problem you state now
is what  the previous response addressed.

What you want to do is change your formula  instead of
  D57:   =SUMB(D2:D56)
use
   D57:  =SUM(D$2:OFFSET(D57,-1,0))
then you insert a row immediately above  your total row
without resorting to maintaining an empty row immediately before
your totals.    More information in
    http://www.mvps.org/dmcritchie/excel/offset.htm
    http://www.mvps.org/dmcritchie/excel/insrtrow.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"klam" <klam@discussions.microsoft.com> wrote in message news:1998B4E2-4E74-422E-90E1-DFABA3441087@microsoft.com...
> Thx for the reply letto115.
>
> Strange for mine, my Sum (row 57) includes row 56, and when I insert rows
> they are not included in the formula.
>
> I did a test file to see if I didn't include the row before, if newly
> inserted rows would be included in the formula, and it did not work either.
>
> Any other ideas?
>
> cheers,
>
> "letto115" wrote:
>
> > I find that if I don't include the cell right above my sum cell, that
> > happens.  If I include that cell, the additional rows I add are included in
> > the total and I don't have to adjust the formula.
> >
> > "klam" wrote:
> >
> > > I use SUM(Column_Title) a lot as I often do not know how many rows the
> > > worksheet will grow to.  It works great but when I add in rows, and
> > > double-click on the formula cell, the extra rows I added are not included in
> > > the formula range (the colour-highlighted box).
> > >
> > > My workaround is to double-click on the formula, then double-click the
> > > colunm title (in Row 1).  Then when I double-click the formula again, it
> > > shows it formula range includes the rows I added.
> > >
> > > This has been happening for a while, and the workaround is tiresome as there
> > > are many columns in some of my worksheets (and I have to double-check to make
> > > sure I didn't miss any).  I have tried F9 and CalculateSheet, both have no
> > > effect.  Is there some way to get it to update automatically?  (I thought
> > > that was the point of it, rather than having to input a cell range.)
> > >
> > > Excel 2000 in XP.
> > > cheers,


0
8/25/2005 5:56:28 PM
Wow, I didn't know it was so complicated!  Thx a mint for your response 
David; it took me a while to read thru your links.  The offset solution 
certainly works!  And, it will be a well-used solution in my work.

letto15:  Sorry I'm such an unsophisticated user that I didn't even 
understand your reference to be the solution!

cheers all,

"David McRitchie" wrote:

> Your posting was too ambiguous before but the problem you state now
> is what  the previous response addressed.
> 
> What you want to do is change your formula  instead of
>   D57:   =SUMB(D2:D56)
> use
>    D57:  =SUM(D$2:OFFSET(D57,-1,0))
> then you insert a row immediately above  your total row
> without resorting to maintaining an empty row immediately before
> your totals.    More information in
>     http://www.mvps.org/dmcritchie/excel/offset.htm
>     http://www.mvps.org/dmcritchie/excel/insrtrow.htm
> 
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
> My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
> 
> "klam" <klam@discussions.microsoft.com> wrote in message news:1998B4E2-4E74-422E-90E1-DFABA3441087@microsoft.com...
> > Thx for the reply letto115.
> >
> > Strange for mine, my Sum (row 57) includes row 56, and when I insert rows
> > they are not included in the formula.
> >
> > I did a test file to see if I didn't include the row before, if newly
> > inserted rows would be included in the formula, and it did not work either.
> >
> > Any other ideas?
> >
> > cheers,
> >
> > "letto115" wrote:
> >
> > > I find that if I don't include the cell right above my sum cell, that
> > > happens.  If I include that cell, the additional rows I add are included in
> > > the total and I don't have to adjust the formula.
> > >
> > > "klam" wrote:
> > >
> > > > I use SUM(Column_Title) a lot as I often do not know how many rows the
> > > > worksheet will grow to.  It works great but when I add in rows, and
> > > > double-click on the formula cell, the extra rows I added are not included in
> > > > the formula range (the colour-highlighted box).
> > > >
> > > > My workaround is to double-click on the formula, then double-click the
> > > > colunm title (in Row 1).  Then when I double-click the formula again, it
> > > > shows it formula range includes the rows I added.
> > > >
> > > > This has been happening for a while, and the workaround is tiresome as there
> > > > are many columns in some of my worksheets (and I have to double-check to make
> > > > sure I didn't miss any).  I have tried F9 and CalculateSheet, both have no
> > > > effect.  Is there some way to get it to update automatically?  (I thought
> > > > that was the point of it, rather than having to input a cell range.)
> > > >
> > > > Excel 2000 in XP.
> > > > cheers,
> 
> 
> 
0
klam (18)
8/25/2005 7:25:01 PM
Reply:

Similar Artilces:

highlight range, apply calculation to data in cells and paste special to same range
I know how to select a range of cells and copy: Range("O2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy but now 1. applying the calculation, which should be value of cell * 1000 2. special past values only to same selected sells ? You enter 1000 in a separate blank cell and copy it then highlight the range to be multiplied and past special-> multiply. that will multiply all the cells by 1000 in the pasted range. Regards, OssieMac "S Himmelrich" wrote: > I know how to select a range of cells and copy: &g...

Highlighting the active row with special colour
Hi there Is there a macro that highlights the whole active row (not only the active cell) that the cursor is in, in an Excel worksheet? Thanks Raj Highlight it in a particular colour, I meant... that would move along when I would move the cursor onto another row.... "Raj Mazumdar" wrote: > Hi there > > Is there a macro that highlights the whole active row (not only the active > cell) that the cursor is in, in an Excel worksheet? > > Thanks > > Raj Try this '---------------------------------------------------------------- Private Sub Workshe...

Re: How to get all mails, even those for unknown recipient
Yes, good idea :) It is all i need (for example http://www.christensen-software.com/cscatchall.htm) But i wish those NDR are generated. To inform user that used address might be misstyped Is it posible to combine catchall mailbox with a ability of standard NDR generating? TIA Martin Blackstone - MVP wrote / napisal(a): > I think you are talking a Catch All mailbox. > Check this out > http://mostlyexchange.blogspot.com/2005/04/creating-catch-all-mailbox-in-exchange.html > > > "guzzi" <guzzi@_DOT_yandex.ru> wrote in message news:OoSFMGZhGHA.12...

Calculating working hours #2
Hi All, I'm hoping someone can help me with the following problem. I need to create a formula which calculates the different between two date/time values excluding out of business hours. Here's an example. A1: 21/06/2005 10:00:00 A2: 23/06/2005 14:30:00 When using "=(B1-A1)*24" I can the result 52.5 If the business hours are between 08:30 and 17:00 the actual result required is 21.5 I hope this makes sense. Thanks in advance, Mo.. Try this =(INT(A2)-INT(A1))*8.5+(MOD(A2,1)-MOD(A1,1))*24 -- HTH Bob Phillips "Mohammed Zenuwah" <MohammedZenuwah@discu...

Over Re-Indexing databases 2147217900
Hi Am getting the following error on a customer site, how can I fix this? could not allocate space for object '(system table id: 2)' in database RMS_ST_customer because primary file group is full C drive had 5GB space on server Other drive has 160GB 2GB RAM Properties of database: unrestricted file growth by 10% automatically grow file The recovery mode is set to simple -- Jenny hi Jennydat, Can you check how big your database is, second what version of SQL Server you are using if MSDE it is not accpeting more than 2 GB space. Make sure to check the data file and log files. &...

Auto Calculate Monthly Time Pace
I need to show how far into a month we are at any given time in % form. I want this to change every day automatically. Example: Today is day 27 of 31 days in January - 87% if today was 2/17/10 it would need to show 17/28 or 61%, 12/20/10 would be 20/31 or 65%, and so on for each day throughout 2010 (12/20/10 would be 65%) Is there a formula or function to do this? Thanks! Heather "HeatherJ" wrote: > I need to show how far into a month we are at any given time in % form. [....] > Today is day 27 of 31 days in January - 87% One way: =day(today()) / ...

Calculate number of months
Hello, I have a field where I am trying to implement a calculation. I want it to take the date in a cell and subtract it from today's date to show me the total number of months between those two dates. So right now, it looks like =(TODAY())-D3 and it returns 167 - the total number of days. How do I make it show me months? THANK YOU IN ADVANCE =Month(Today())-Month(D3) "TxWebDesigner" <beverly@beverlylanedesigns.com> schreef in bericht news:e3fR#jXWJHA.5032@TK2MSFTNGP05.phx.gbl... > Hello, > > I have a field where I am trying to implement a calculation...

RE: Use this critical package for Internet Explorer
--hmrlmpxejw Content-Type: multipart/related; boundary="enmgzrgmzgzwwvlg"; type="multipart/alternative" --enmgzrgmzgzwwvlg Content-Type: multipart/alternative; boundary="hsqxuryiiedx" --hsqxuryiiedx Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Partner this is the latest version of security update, the "October 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 help protect y...

Rows and Columns Settings Problem
How do you set rows and columns in a way that when you scrol down/column you can always see a certain row(s)/column(s) -- Message posted from http://www.ExcelForum.com Check out XL Help for "Freeze Panes" In article <JMorgan.1ad5vf@excelforum-nospam.com>, JMorgan <<JMorgan.1ad5vf@excelforum-nospam.com>> wrote: > How do you set rows and columns in a way that when you scroll > down/column you can always see a certain row(s)/column(s). Thank -- Message posted from http://www.ExcelForum.com ...

Question re:clustered column w/3D visual effect
I have Excel 2003 Why wont the 3D chart allow you to drag it more open? There is a large open area between the left side and the axis that looks jerky :) In a plain clustered column chart you can click inside to make the frame appear and drag it larger or smaller as you desire. In the 3D, clicking only allows you to change the angles of the 3D box. Am I missing something? thanks, Meenie This should be a hint to avoid the 3D effects. 3D charts are inflexible, but more important, the 3D effects mask the information in the chart. - Jon ------- Jon Peltier Peltier Technical Services, Inc....

more than 65,536 rows
Hi Is it possible to have more than 65,653 rows on a worksheet? The data I have does not fit on a normal sheet Harald Harald, Unfortunately, the maximum number of rows on a worksheet is 65,536 rows and cannot be increased. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Harald Bock" <anonymous@discussions.microsoft.com> wrote in message news:D6E9402F-92F4-4649-A72C-BC38BE6C89EB@microsoft.com... > Hi, > > Is it possible to have more than 65,653 rows on a worksheet? The data I have does not...

Minimizing Rows of with similar data
I am using Excel 2007. I have 3 columns A, B, and C such as below: TU10-10 TU10 Tungsten Satin Wedding Band TU10-10.5 TU10 Tungsten Satin Wedding Band TU10-11 TU10 Tungsten Satin Wedding Band TU10-11.5 TU10 Tungsten Satin Wedding Band TU10-12 TU10 Tungsten Satin Wedding Band TU10-12.5 TU10 Tungsten Satin Wedding Band TU10-13 TU10 Tungsten Satin Wedding Band TU10-6 TU10 Tungsten Satin Wedding Band TU10-6.5 TU10 Tungsten Satin Wedding Band TU10-7 TU10 Tungsten Satin Wedding Band TU10-7.5 TU10 Tungsten Satin Wedding Band TU10-8 TU10 Tungsten Satin Wedding Band TU10-8.5 TU10 Tung...

insert logo using AUTOCORRECT
Have been trying to insert my company's logo using AUTOCORRECT. It works like a charm in WORD. It doens't work in Excel. ex. when I type "lg" these characters should be replaced by my company's logo. Does anyone know how to do this in Excel? -- digicat ------------------------------------------------------------------------ digicat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14920 View this thread: http://www.excelforum.com/showthread.php?threadid=497273 ...

How do I filter rows based upon a column value
I have a spreadsheet that contains multiple agency id's in a column. When generating reports, I would like to filter per agency and display only the rows associated with that agency. Is there a tutorial or sample on how to do this? Hi It sounds like you are looking for Data / Filter / AutoFilter. Have a look here for some basics: http://www.contextures.com/xlautofilter01.html -- Andy. "Jack" <nfr@nospam.com> wrote in message news:eqiU08TVEHA.2988@TK2MSFTNGP10.phx.gbl... > I have a spreadsheet that contains multiple agency id's in a column. When > generati...

re-download MS Money instructions??
I purchased and "installed" MS Money Deluxe today on the Internet. I have the order confirmation and codes in hand. After removing my prior version of Money Deluxe 2006 ..I received a message saying that Money could not "install files needed" and for me to "re-download the program". Where/how do I "re-download: the program? the M S Money page that I found tells me to get the code and purchase information in hand...but..stops there. Is there a site I go to re- download? thanks for any help. Rich In microsoft.public.money, rich wrote: >I pur...

DVD Insert
I have been asked by a friend to design an insert for a DVD he is making. The only templates I've been able to find are the ones that are actually for CD jewel cases. He is going to be using the DVD cases like you see in the stores when you buy a movie, which is much larger and the insert goes into the plastic sleeve. It's about 10-1/2" X 7-1/4". I checked the Microsoft site, but they had nada. Does anyone have a place where I could get a template for this project, or any idea of the best way to set this up? I've done CD inserts, but never a DVD before. Thanks....

how would you calculate the number of hits to your website
Hi ,' can some one tell to me the answer of this question On a website, how would you calculate the number of hits to your website ...

List all row source for all forms, reports etc.
Hi I'm doing some work cleaning out old unused forms, reports and queries. I have been going through each report in a database (There are A LOT), determining its row source query, then marking it for deletion. I will eventually end up going through and deleting all the unused queries. As you can imagine this is time consuming, and I was thinkg 'there must be a FASTER way" Does anyone have a suggestion, or link to a pre-built function or model that could assist me? Regards Darragh On Thu, 17 Jan 2008 22:14:18 -0800 (PST), Darragh <darragh.murray@gmail.com> wrote: >Hi...

Returning Data from a third cell in same row that meets two other
I'm looking for a formula that will return a result from a cell on a same row as two other cells that meet certain criteria. I'm sure there is a way to do this but i am a novice at this kind of stuff and can't seem to figure it out. For example i want a cell to = what is in column E when column A="36751" and when column B="Total Returns" The spreadsheet has 55000 rows. there will only be one instance where both these criteria are met. i want to use this to create a seperate spreadsheet with just info i need and can update on a daily basis. thanks in ...

Insert hyperlink greyed out
Can't see any other posts on the above any advice valued. Thanks {XP2, with Publisher 2002} On Sun, 23 Oct 2005 08:39:37 +0100, Gel wrote (in article <1130053177.481108.189290@o13g2000cwo.googlegroups.com>): > Can't see any other posts on the above any advice valued. > Thanks > > {XP2, with Publisher 2002} > Ah yes, "can't see' the classic euphemism for "I'm a lazy f**kwit, I can't be arsed to RTFH, or to STFW. Please wipe my arse for me, because I'm too lazy to find the bog roll..." Where are you attempting to insert th...

How to get Row # in formula?
I have several rows with the same formula but they are seperated, in some cases, by rows with other information to prevent me from just dragging a formula down to other rows. Ex. While in row 13: B13*C13+B13*D13+B13*E13 While in row 20: B20*C20+.... how can I write a formula to populate the "13" or "20" automatically for whatever row I am in? Thanks, Dave You may not be able to drag it, but if you copy row13 formula and go to row 20 it updates does it not? -- HTH Bob "DaveR" <DaveR@discussions.microsoft.com> wrote ...

Tax calculation for Quotes, Orders, Invoices
Although CRM v3.0 allows tax to be keyed in to an Order, it does not offer any automation in calculating tax for an order based on taxable line items and shipping location. Entering orders is a repetitive and time-consuming event, and so this calculation should be offered as a feature. ---------------- 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 N...

Re: Find and Replace command
Folks, I am wondering if anyone can help me with the following scenario: I downloaded some financial info. from SAP into excel. The negative numbers are showing up as: 585,656.25- I want Excel to replace the ending (-) with a begining (- ). i.e.-585,656.25 I thought that I could type in wildcard characters to replace all the numbers with the ending (-). So: Replace .**- with -**.** While Excel is able to locate the numbers it is just changing it to exact wildcards (e.g. -**.**) I know that I am doing smething wrong..can somebody please help? Thank you. Shans A macro to do all cells...

Calculating Date Fields
I have a document that is filled with FormFields and users go from field to field filling the fields for a final document. I have been reading up on 'calculated dates' but have not seen a scenario like this. Three of my formfields are "Date1", "Date2" and "Date180." What I am trying to figure out is how to evaluate/compare Date1 and Date2 to see which is the earliest and then provide in Date180 the date 180 days from the earliest date. (Unless the form is blank there will always be a Date1, but there may not be a Date2 and if Date2 exist...

Conditions on form calculated textbox
Hello, In a form called Schedule (based on the table Schedule) I have the following textboxes that are bound to the table Schedule: Time In, Time Out, Time Off, Anomaly and Overtime I also have an unbound textbox called HRS that gives me the result of the following equation: =[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] The above result gives me the daily hours that someone worked. The formating is decimal, as an example. 16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0+ 3.0 = 10.0 I also added another figure for lunch the hour [16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0...