How to keep a running total on a col that has frequent new entires

Hello all. This is my first post in the excel groups, so I was not sure 
which group would be most appropriate for my question (so please forgive 
the crosspost).

I want to do something that seems so simple, but I have not yet figured 
out how to best do it.

I will be entering several fields of data such that a1 - b1 = c1 with 
subsequent rows entered as data becomes available such that the next row 
would be a2 - b2 = c2, and so on.  I want a total of column 'C' such that 
every day when I enter a row of new data that the total of all entries in 
column 'C' moves down as new rows are added but still keeps the correct 
column total.

I have thought to make this happen by writing a macro that gives the user 
a data entry form, looks for the last row of data and inserts the new 
data, and then moves the column total formula down one row as needed.  
But I can't help but think there is a more elegant/simple way to do this 
(and my VBA skills are really rusty).

TIA for your help and advice on this matter.

James

0
jbond (1)
8/20/2004 8:03:26 AM
excel 39879 articles. 2 followers. Follow

5 Replies
598 Views

Similar Articles

[PageSpeed] 36

Hi
do you want this total formula as last item in column C?. The easies
way would be to have this total at the TOP of column B and using a
formula such as:
=SUM(C2:C10000)

--
Regards
Frank Kabel
Frankfurt, Germany

"James Bond" <jbond@ue.com> schrieb im Newsbeitrag
news:Xns954B29596C7ACjbonduecom@63.223.5.244...
> Hello all. This is my first post in the excel groups, so I was not
sure
> which group would be most appropriate for my question (so please
forgive
> the crosspost).
>
> I want to do something that seems so simple, but I have not yet
figured
> out how to best do it.
>
> I will be entering several fields of data such that a1 - b1 = c1 with
> subsequent rows entered as data becomes available such that the next
row
> would be a2 - b2 = c2, and so on.  I want a total of column 'C' such
that
> every day when I enter a row of new data that the total of all
entries in
> column 'C' moves down as new rows are added but still keeps the
correct
> column total.
>
> I have thought to make this happen by writing a macro that gives the
user
> a data entry form, looks for the last row of data and inserts the new
> data, and then moves the column total formula down one row as needed.
> But I can't help but think there is a more elegant/simple way to do
this
> (and my VBA skills are really rusty).
>
> TIA for your help and advice on this matter.
>
> James
>

0
frank.kabel (11126)
8/20/2004 9:55:39 AM
Hi
see your other post in Excel.worksheetfunctions

--
Regards
Frank Kabel
Frankfurt, Germany

"James Bond" <jbond@ue.com> schrieb im Newsbeitrag
news:Xns954B29596C7ACjbonduecom@63.223.5.244...
> Hello all. This is my first post in the excel groups, so I was not
sure
> which group would be most appropriate for my question (so please
forgive
> the crosspost).
>
> I want to do something that seems so simple, but I have not yet
figured
> out how to best do it.
>
> I will be entering several fields of data such that a1 - b1 = c1 with
> subsequent rows entered as data becomes available such that the next
row
> would be a2 - b2 = c2, and so on.  I want a total of column 'C' such
that
> every day when I enter a row of new data that the total of all
entries in
> column 'C' moves down as new rows are added but still keeps the
correct
> column total.
>
> I have thought to make this happen by writing a macro that gives the
user
> a data entry form, looks for the last row of data and inserts the new
> data, and then moves the column total formula down one row as needed.
> But I can't help but think there is a more elegant/simple way to do
this
> (and my VBA skills are really rusty).
>
> TIA for your help and advice on this matter.
>
> James
>

0
frank.kabel (11126)
8/20/2004 10:13:16 AM
i use a very basic solution to these kinds of issues. 

i make the first row show the total for the column. that is, the firs
row has the total, the second row as the headers, data start from thir
row onwards. this way i can avoid  macros and complexity

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

0
8/20/2004 10:54:51 AM
Put your sum at the top.  Make life simple.

-- 
Regards,
Tom Ogilvy

"James Bond" <jbond@ue.com> wrote in message
news:Xns954B29596C7ACjbonduecom@63.223.5.244...
> Hello all. This is my first post in the excel groups, so I was not sure
> which group would be most appropriate for my question (so please forgive
> the crosspost).
>
> I want to do something that seems so simple, but I have not yet figured
> out how to best do it.
>
> I will be entering several fields of data such that a1 - b1 = c1 with
> subsequent rows entered as data becomes available such that the next row
> would be a2 - b2 = c2, and so on.  I want a total of column 'C' such that
> every day when I enter a row of new data that the total of all entries in
> column 'C' moves down as new rows are added but still keeps the correct
> column total.
>
> I have thought to make this happen by writing a macro that gives the user
> a data entry form, looks for the last row of data and inserts the new
> data, and then moves the column total formula down one row as needed.
> But I can't help but think there is a more elegant/simple way to do this
> (and my VBA skills are really rusty).
>
> TIA for your help and advice on this matter.
>
> James
>


0
twogilvy (1078)
8/20/2004 12:16:07 PM
James

Yes definitely keeping the total at the top is the easiest way.
However, if you have your heart set on moving the total down with each
entry, I have a way.  It's not pretty but it works.

Here we go.

In the first row of data (let's say row 1) just set c1= a1-b1.
However in the second row (2) enter the following:
    =IF(ISBLANK(A2),IF(ISBLANK(A1),"",SUM(C$1:C1)),A2-B2)

Copy this formula down the column for as long as you might need.

The above formula gives the column a split-personality with 3 possible
outcomes:
1)  If there is data in column A of its row, it will output A-B.
2)  If there is no data in column A in its row but there is data in the row
above, it calculates the total of the column C data above it.
3)  If there is no data in column A of its row and the row above, it returns
a blank cell.

As I said before, it's not pretty and restricts formatting a lot.   If you
want it to look pretty, put the total at the top.

A similar solution involves placing the total in Column D.  Eg, D1 would
have:
    =IF(ISBLANK(A2),IF(ISBLANK(A1),"",SUM(C$1:C1),"")

This would place the total next to the last row of data only, and would
allow you to format the total differently to the data.

Good luck!
_______________________________________________________________

"James Bond" <jbond@ue.com> wrote in message
news:Xns954B29596C7ACjbonduecom@63.223.5.244...
> Hello all. This is my first post in the excel groups, so I was not sure
> which group would be most appropriate for my question (so please forgive
> the crosspost).
>
> I want to do something that seems so simple, but I have not yet figured
> out how to best do it.
>
> I will be entering several fields of data such that a1 - b1 = c1 with
> subsequent rows entered as data becomes available such that the next row
> would be a2 - b2 = c2, and so on.  I want a total of column 'C' such that
> every day when I enter a row of new data that the total of all entries in
> column 'C' moves down as new rows are added but still keeps the correct
> column total.
>
> I have thought to make this happen by writing a macro that gives the user
> a data entry form, looks for the last row of data and inserts the new
> data, and then moves the column total formula down one row as needed.
> But I can't help but think there is a more elegant/simple way to do this
> (and my VBA skills are really rusty).
>
> TIA for your help and advice on this matter.
>
> James
>


0
dandandy (1)
8/20/2004 2:40:18 PM
Reply:

Similar Artilces:

Outlook Express Visual C++ Runtime Library Run-Time Error
I have a friend who's using Outlook Express 6 and is getting an "abnormal program termination" error whenever he attempts to attach a graphic file within Outlook Express. He reports that it works fine when using the Explorer shell to call OE. My friend has WinXP Home. Any advice? Jay ...

New Workbook When Opening Excel
Hi, Somehow I've lost my "new" workbook when I open excel. It opens with grey in the workbook space and Getting Started on the right. I'd like it to open with a new workbook ready to go, no Getting Started, but can't figure out how. Thanks. Marti Check to see if you have a workbook in your XLStart folder. If you do, move it (or delete it??) Marti wrote: > > Hi, > > Somehow I've lost my "new" workbook when I open excel. It opens with > grey in the workbook space and Getting Started on the right. I'd like > it to open with a n...

Slow mail delivery from new Exch 2003 box
I have Exch 2003 installed on my network on a Win2000 server. I've just installed Exch 2003 on a 2003 member server joined to that domain. Now I have two Exch 2003 servers in my Org. I created a test mailbox account on the new Exch 2003 server. Whenever I send email from the test account, it is very slow to arrive (28mins). If I move that mailbox to the old Exch 2003 on the Win2000 server, it arrives to the recipient instantly. If I run the DCDiag & Netdiag tools; Is there anything in particular in need to look for? Please Help anyone! Post some more info on your AD infrastuct...

Long delay in opening New Message windows
My Windows mail has recently developed the problem of taking 10 seconds or more just to open a create new message window. How do I fix this? Did you add any 3rd party software into the Add Ins -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "Jembeaux" <Jembeaux@discussions.microsoft.com> wrote in message news:A58C3591-9431-4BDF-8D32-9ACE1E12D41E@microsoft.com... > My Windows mail has recently developed the problem of taking 10 seconds or > more just to open a create ne...

run macros automatically
i have got around 10 macros in a excel spreadsheet. i want all of them to run when i open the file. or there should be a button which will runs all the macros whenever the user wants. You can do it either way really, First create a new macro, that runs all the ten macros that are in your workbook. If you want this macro to run whenever the sheet is opened, save it with the name 'Auto_Exec' and it will run automatically when you open the book. If you want to execute the macro on a button press, you can call it any name you wish. Next you will need to create a new button, so go ...

Keeping the format but update the data
I have a report I run everyday. I want to update the data, but keep the column sizes and formatting. Any suggestions? Anybody suggest a good ebook to become more formuliar with excel? JoeM JoeM, this could be as simple as recording a macro during the formatting of the sheet after importing the data once. Then each day, after importing the new data, you simply run that macro. As for online eBook's for Excel, I'm not actually aware of any although I suspect a Google search would turn up several. It's hard to beat the "For Dummies..." series of hardcopy...

Move worksheet to new book
I have 14 worksheets within a workbook. In the middle are worksheets named Sheet1, Sheet2....Sheet10 Is it possible using a macro to see if there is anything written in cell A1 on Sheet1, and if there is, then move it to a new book, and then check Sheet2 and so on? And if there is no information on the worksheet I would like to delete it. TIA Try this against a copy (since it destroys worksheets): Option Explicit Sub testme01() Dim wks As Worksheet Dim iCtr As Long Dim wkbk As Workbook Set wkbk = ActiveWorkbook For iCtr = 1 To 10 'sheet1 throu...

Installed Exchange 2003 Server on new Hardwar... How to get rid of old Exchange 2000 Server?
Hi, Our single domain company (Win 2000 DC's) was running a single Exchange 2000 server. I bought a new server, installed Windows 2003 Server on it, joined it to the domian (non-DC) and then installed Exchange 2003 Server on that and joined it to the site. I then pointed our MX records towards the new box. As soon as I verified connectivity, I began moving user's mailboxes over 1 by 1. Everyone is now running smoothly on the new server. I have not as of yet removed the old server (still on, running, no mialboxes). The problem is, that as soon as I turn off the old Exchange 2000 s...

new threads.....
It is wierd and I would rather I didn't but for some bizarre reason reply group is not working Fix your application, Danny. :) ******************* ~Anne Troy www.OfficeArticles.com "DannyJ" <youhavegottobekidding@nospam.com> wrote in message news:eY0ykmYiFHA.3216@TK2MSFTNGP10.phx.gbl... > It is wierd and I would rather I didn't but for some bizarre reason reply > group is not working > > ...

Run two copies of Outlook with different profiles
Hello! I'm trying to run two copies of Outlook with different (outlook)profiles at the same time. Normally i can choose the profile with the /profile switch, but if there is already a copy of outlook running, this switch seems to be ignored. Any ideas how to handle this are welcome. Regards, Reinhard "Reinhard" <reinhard.spieker@bkvibro.de> wrote in message news:60a7e218.0311190408.6523100b@posting.google.com... > Hello! > > I'm trying to run two copies of Outlook with different > (outlook)profiles at the same time. > > Normally i can choose the p...

does publisher 2000 run on windows xp?
thanks for helping if you know the answer Yes, all versions of Publisher run on XP, Publisher 1.0 maybe the exception. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Baudouin" <Baudouin@discussions.microsoft.com> wrote in message news:6AA35200-15D7-47F2-8471-4E6737171B6A@microsoft.com... > thanks for helping if you know the answer Mary Sauer <gsauer@mycolumbus.rr.com> was very recently heard to utter: > Yes, all versions of Publisher run on XP, Publisher 1.0 maybe the > exception. It runs. Not on...

Problems switching to new hardware
after removing active directory from the machine, I tried to activate it as a 2nd machine for the new Dell machine. No luck, it still could not locate the machine. After tinkering around I finally gave up and reinstalled the OS on the new machine from scratch. I recreated the user list and also installed the email server. We're up and running again. I only have one problem left. Our old domain was server1.xxx.org. The new domain I created was just xxx.org. So now anytime we try to access the external web site from our provider called xxx.org I don;t get it. I've tried to use the DNS ma...

Outlook keeps making itself Default
Hi, Everytime I start Outlook it takes over as default for email and addresses even though I have that option unchecked in outlook, and in IE I set OE Express as default, yet each time I launch outlook it takes over as Default, any ideas TIA Wayne ...

Excel97--Each workbook opens new application
Excel 97: Each xls file opens a separate copy of Excel. How do I get multiple workbooks to open in one application? Jim Tools>Options>General. Uncheck "Ignore other Applications" Gord Dibben Excel MVP On Mon, 19 Jan 2004 16:27:39 -0800, "JimL" <anonymous@discussions.microsoft.com> wrote: >Excel 97: Each xls file opens a separate copy of Excel. >How do I get multiple workbooks to open in one application? ...

Bypass New Presentation Task Pane
Our issue is with File > New in PowerPoint. The New Presentation task pane is confusing to end users. Has anyone implemented a solution that bypasses the New Presentation task pane and displays the PowerPoint templates? Thanks, Joe -- Manager IT Quality Assurance The Segal Company ...

Trying to get a type of video file format to open and run on my we
I created a video using Windows Movie maker and saved as .wmp file. I saved it to my computer and then inserted on my web page under Front Page 2003. Nothing! I then converted the file to avi format - still nothing! I then tried mpeg4 still nothing. The manual is next to useless on this subject. Anyone tell me what I need to do so that when a user opens up the page the video plays automatically. -- Simon Holloway Save as a .wmv and insert a suitable player into the page. For IE only, Insert->Web Component->Advanced Controls->ActiveX Control Click Next Choose Window...

script error running reports from home pages, why??
I'm not a GP user/expert, we recently installed GP2010. Since then one user has said, "When I try to run reports from the home pages, I am getting this script error and cannot run any reports." The error is: Line 47 Char 5 Error The system cannot find the file specified Code 0 URL: file:///C:/Users/username/AppData/Local/Temp/tmp/AE8.tmp I have received differing responses to this issue: 1) enable the Run command on the Start menu of our XenApp server for the regular domain users, which I view as a big security risk 2) "the UserData folder does not exist in the %ap...

"No indexers" on creating "New Shared Services Provider "
Hi to all, I've this problem, I what to create the new and first "Shared Services Provider", but on "Index Server" section I don't find any Index Server (in combo-box). What can I do? Where am I wrong? Bye Dario Concilio Make sure you configured and started the Search services. -- Daniel A. Galant Imagine what we could be... if we could just imagine. "Dario Concilio MCP" <dario.concilio@hotmail.it> wrote in message news:uhcKStDzKHA.5040@TK2MSFTNGP02.phx.gbl... > Hi to all, > I've this problem, I what to creat...

Copy cell to a new cell
Hi I have a cell value in the format 90TN82C128004K00. I would like to copy the first two digits to another cell i.e. 90 and follow it up with a dummy code 00CPSS00 which will be standard for all values. the new cell should have the following value 9000CPSS00. Is there an automated function that can do this for me. Hi, If you data is in A1 enter in B1: =LEFT(A1,2)&"00CPSS00" Regards, -- AP "mohd21uk" <u20517@uwe> a �crit dans le message de news:5e5292da6a2d1@uwe... > Hi > > I have a cell value in the format 90TN82C128004K00. I would like to copy the ...

OWA using a new Web Site
I have recently Installed SBS 2000, by default OWA is operational from the default web site. I have created several new sites in IIS and created the necessary virtual directories to gain access to the exchange mail server. However when I try to go in via a web browser I get the folder/file listing view in IE rather than the templates. All of the settings between the default virtual folders and the domain I wish to use are the same and I am stumped. I can still access the template from the server by going to the address http://localhost/Exchange at the server Any assistance would be apprec...

sum of directories does not equal disk total?
I want to clean up my drive and to find out where all the usage was I did a "Properties"inquiry on all the directories. The sum of these is short of the total by 48 of 168 gig used on a 250 gig drive. How do I find out what is using the 48? Thanks Andy AndyM wrote: > I want to clean up my drive and to find out where all the usage was I did a > "Properties"inquiry on all the directories. The sum of these is short of the > total by 48 of 168 gig used on a 250 gig drive. How do I find out what is > using the 48? > Thanks > Andy AndyM wrote: ...

keep lover level bullet list with previous bullet
Word 2007. I have a Heading then some paragraphs. Then a bullet list. How do I prevent a page from breaking in the middle of a lower level bullet list? In other words, how do I keep lower level bullets with it's upper level? For example: Many paragraphs o upper level bullet aaa - one aaa - two aaa - three aaa o upper level bbb - one bbb - two bbb - three bbb Right now I run out of room on the page and -two bbb goes to the next page. I can highlight the "bbb"s and under Paragraph/line and Page Breaks check Keep Lines Together. I may nee...

Disable the "New Lead" button on the home_leads.aspx
Hi All, How can I disable the following 1.- "New Lead" button on the grid menu bar. Prioror to the print button? 2.- Disable / alter the menuitems in the "New" menu bar 3.- Disable / alter the menuitems in the "Actions" menu on the grid menu bar One way is setting the rights for the users. Is there any other way to acheive this say by HttpModule handler??? and how to do this.. Thanks in advance.... Prasad ...

Totals Query Help!
Hi, I have a table with the following fields: Manufacturer Model Value SoldMonth What I'm trying to do is write a query that will give me an average Value for each month, but I want the average to be based only on records where the Model is consistent across all months. So if Model x is missing from one or more months all records for that model would be excluded from all the overall Monthly averages. So the query will only average records where the Model is present in each month. Any help with this would be greatly appreciated. Regards.....Jason It'll help us if you can ...

Brand New to OL 2007
Is there a way to subscribe to some of the conversations here using OL 2007? I have recently found these discussion pages and am reeling from all of the info, but do not want to go to the MS web site every time to look for something. I am new to the whole RSS, newsgroup thing, but learning every minute. You can subscribe, but it doesn't always notify you. Better is to use an NNTP client to read the groups. You can mark threads to watch or drag them to a mail folder (in OE) to review later. If a thread has an answer and you want to save it, create a folder in Outlook then copy a...