Totalling columns and repeating formulas in new entries

i'm working in excel 2003

i've tried searching this, but have come up with nothing.  it seems s
basic, i'm sure i must be calling it the wrong thing or something.

in my worksheet (that's what a spreadsheet is called now, isn't it?) 
enter expenses in column "e" and revenues in column "i", with the ne
gain or loss for that row (entry) showing up in column "j".  i had n
problem setting that up.

the first problem i'm having is the formulas and formatting bein
copied to the next entry.  some of the new entries have no value i
column "e", column "i" (or both).  after one of those entries wit
missing values, the next entry will not copy the formulas or formattin
from the last entry with data.  I'm assuming the "3 entries" in a ro
format has been broken.  is there a way to make the formulas an
formatting in each row default to each new entry?

the second problem i'm having is sorting.  entries are sorted first b
date, then by time.  there are no other sorts.  some of my entries ar
put in out of sequence.  i'd like the worksheet to automatically sor
each new entry as it's entered.  is this possible?

the third problem is totals.  try as i might, i cannot figure out ho
to make the bottom row show totals of the columns.  i'd like the las
cell in columns "e", "i" and "j" to show the totals of each respectiv
column.  i'd also like this "totals" row to stay below the last entr
in the workbook as new entries are made.

finally, i'd like a "monthy subtotals" row to embed in the workboo
immediately after the last day of each month and remain there.

can anyone help me?

thank

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

0
7/24/2004 11:51:20 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
594 Views

Similar Articles

[PageSpeed] 0

Instead of depending on the Auto Extend feature, you could use Excel 
built in data form (Data>Form). It will fill in all the formulas for 
you. Or download John Walkenbach's Enhanced Data Form. It's a free 
add-in, allows combo boxes, inserts new rows with formulas, and has 
unlimited fields.

   http://j-walk.com/ss/dataform/index.htm

An autosort would only be possible if you add macros to the workbook. 
Instead, you could sort the list after you've finished the batch of new 
entries.

Define your table as a list (Data>List>Create List)
Select a cell two rows down from the end of the list
Enter a SUBTOTAL function in each column that you want to total.
   For example:  =SUBTOTAL(9,E2:E200)
Add new rows to the end of the list, and the Totals will automatically 
move down.

For monthly subtotals, you could insert a row at the end of each month's 
entries.
Use the SUBTOTAL function to sum the entries for the month, e.g.:
     =SUBTOTAL(9,E2:E20)


seanof30306 < wrote:
> i'm working in excel 2003
> 
> i've tried searching this, but have come up with nothing.  it seems so
> basic, i'm sure i must be calling it the wrong thing or something.
> 
> in my worksheet (that's what a spreadsheet is called now, isn't it?) i
> enter expenses in column "e" and revenues in column "i", with the net
> gain or loss for that row (entry) showing up in column "j".  i had no
> problem setting that up.
> 
> the first problem i'm having is the formulas and formatting being
> copied to the next entry.  some of the new entries have no value in
> column "e", column "i" (or both).  after one of those entries with
> missing values, the next entry will not copy the formulas or formatting
> from the last entry with data.  I'm assuming the "3 entries" in a row
> format has been broken.  is there a way to make the formulas and
> formatting in each row default to each new entry?
> 
> the second problem i'm having is sorting.  entries are sorted first by
> date, then by time.  there are no other sorts.  some of my entries are
> put in out of sequence.  i'd like the worksheet to automatically sort
> each new entry as it's entered.  is this possible?
> 
> the third problem is totals.  try as i might, i cannot figure out how
> to make the bottom row show totals of the columns.  i'd like the last
> cell in columns "e", "i" and "j" to show the totals of each respective
> column.  i'd also like this "totals" row to stay below the last entry
> in the workbook as new entries are made.
> 
> finally, i'd like a "monthy subtotals" row to embed in the workbook
> immediately after the last day of each month and remain there.
> 
> can anyone help me?
> 
> thanks
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 


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

0
dsd1 (5911)
7/25/2004 2:32:42 AM
Reply:

Similar Artilces:

Moving a value in Column D
I have a spreadsheet with values in a couple of cells in Column E. The rest of the cells are blank. I would like a macro that would move the value of any cell containing data (non empty cells) four cells backwards and two cells downwards to Column A. Is there a macro that would do this. -- Message posted via http://www.officekb.com Sub MoveIt() Dim rCell As Range For Each rCell In Columns("E") _ .SpecialCells(xlCellTypeConstants) rCell.Offset(2, -4).Value = rCell.Value rCell.ClearContents Next rCell End Sub HTH -- AP "mohd21uk via OfficeKB.com" <u20517@u...

Formula in Comment
Hi! I would like to customize a comment (Excel) by using a formula. Is tha possible in any way? The solution is supposed to look like this: Entered data in Cell: eg. Steel 1 Comment: weight per foot 10lbs Entered data in Cell: eg. Steel 3 Comment: weight per foot 5 lbs Entered data in Cell: eg. Pipe Comment: weight per foot 1 lbs Entered data in Cell: empty Comment: Enter type of material THX Raine -- rkloes ----------------------------------------------------------------------- rkloess's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=257...

Creating a new template using Publisher 2002
I am writing a new letter head for a law firm and I wanted to know how to create a new template for this new letter head. Hi MSANTACRUZ (MSANTACRUZ@discussions.microsoft.com), in the newsgroups you posted: || I am writing a new letter head for a law firm and I wanted to know || how to create a new template for this new letter head. If you go to File > Save As and change the file type to Publisher Template, this will then save this as a Publisher Template which can be accessed via Start > New Office Document or in Publisher via the File > New > My Templates menu. -- Brian K...

An XML question
I would like to produce the following output based on my XML file: My Album (2005) Elapsed Time (hh:mm:ss): 00:07:00 Song 1: title1 Length (hh:mm:ss): 00:02:30 Song 2: title2 Length (hh:mm:ss): 00:02:15 Song 3: title3 Length (hh:mm:ss): 00:02:15 ===== <album> <general> <title>My Album</title> <year>2005</year> </general> <content> <song> <songTitle>title1</songTitle> <songLengthInSeconds>150</songLengthInSeconds> </song> <song> <...

A Named cell to be in a formula for a code
Hi! I have a named cell -> combo_chk (cell J3) This combo_chk changes because of FOR..NEXT from code but the style i always like this : 4,5,12,40,45 I have 5 columns with values in the rows A B C D E 1 12 40 41 45 5 6 10 11 40 I want to find if the 1 on column A is including in the combo_chk then if the 12 on column B is including in the combo_chk e.t.c. If it is found return '1' otherwise return '0'. So for the above example i would get for the first row -> 0 1 1 0 1 for the second row -> 1 0 0 0 1 because the values from the column B,C and E are incl...

Totals on Sales Inquiry native windows
We would like to have the totals on the Sales Inquiries: Transaction by Customer Transaction by Document Sales Documents Sales Items Today we can retrieve this information in separated inquiries and reports, be we would like to heve a easier way to see this totals... The same suggest can be applied to Smartlist. If possible, you can create a tool to sum a field after you make a filter I created a screen where you can see all sales, separated by day of week, and week of year, and totals YTD,MTD and WTD, but I am not able to have the same information grouped by item number, for example.....

total cost on report
I have areport with a cost field that I would like to total that collum at the end of the report. How do I do this ? do I need to create another field (total) please help thanks, jettro Jettron wrote: >I have areport with a cost field that I would like to total that collum at >the end of the report. How do I do this ? do I need to create another field >(total) The usual way is to use a text box with an expression like: =Sum([cost field]) -- Marsh MVP [MS Access] I'm in design view of the report, where do I insert this expression? I clicked on page footer to insert thi...

VB6 formula not working in excel
Hi, I have a few formulas embedded in my vb6 code which calculate and enter the results in the excel sheet. These formulas work for me and everyone at my workplace. But for some reason these formulas have recently stopped working for some of our model users in other countries. I noted that the problem is only with the formulas that are calculating the percentage, the rest of the formulas are working on the sheet. I also wanted to ask, is there a difference in using N/A and N?A in VB6? I should probably ask this question in the VB forum, but if anyone knows, please advise. Thanks, ...

Re: fuction to copy Column a and b to comment or colum c?
Please help!!! I would like to know a fuction or a way to just recop what ever is in colum a and c to comment in colum d. Is this eve possible? If not maybe a fuction to put values of colum a and c t colum d. Please keep in mind this would prevent me from typing again. Thanks!!! -- Message posted from http://www.ExcelForum.com You can concatenate the values in A & C in column D with a formula: =A1&C1 or =A1&" "&C1 if you want those values in a comment, you'll need a little code. This is a sub (not a function): Option Explicit Sub MakeComments() Dim...

@IF Formula w/ multiple outcomes
Trying to do a basic excel formula that would show the following: If cell A1=A,X,but if cell A1=B,Y,or if cell A1=C,Z Not sure how to work multiple @IF statements versus just a true/fals answer. Thx for any help -- Message posted from http://www.ExcelForum.com Hi try =IF(A1="A","X",IF(A1="B","Y",IF(A1="C","Z","not defined"))) if you need more conditions you may consider using VLOOKUP and a lookup table -- Regards Frank Kabel Frankfurt, Germany "Calicokitty >" <<Calicokitty.16vuja@excelforum-nospam...

Setting ID# to 1 & Total on bottom of query result
Hi, I have 2 separate questions. First how do I set the ID# to 1 for the first record. I copied my whole database including queries and forms which were 2009 records and changed the titles to 2010. I did a "compact and repair" to the 2010 database. But when I start to enter records for 2010 the ID# is 2550. How can I reset the ID# back to start at 1? On many of my queries I have the results be for example 28 Caucation, 43 African American, 65 Hispanic, 4 Asian, 2 Native American. I need these numbers separate like they are but I would like a total line at the bottom...

New update problems
The Print to One NOte option no longer on printer list after 4/14 automatic update. How do I add it to list of printers? smartinup wrote: > The Print to One NOte option no longer on printer list after 4/14 > automatic update. How do I add it to list of printers? Try to run a "Repair" (from Control Center - Software/Programs and Features). Rainald Can you contact me via my blog below? -- Sorry for the problems, John Guin OneNote Test Team http://blogs.msdn.com/johnguin "smartinup" wrote: > The Print to One NOte option no longer on ...

Adding LDAP entries to the Global Address List
I have two exchange 2003 sp1 servers in a a front end and back end configuration running on windows 2003 sp?. I'm running window 2003 ADS. My organization exist in a bigger network environment that uses LDAP server for directory services. My questions, is there a way that I can include the LDAP server information along with the Global Address List. Sort of like configuring a mail clients to pull information from an LDAP server. Is this even possible? "thutchin" <thutchin@discussions.microsoft.com> wrote: >I have two exchange 2003 sp1 servers in a a front end...

macro
I have several columns in an income sheet. One column has a sum at the bottom. I want to be able to run a simple macro that will copy the last row of formatted cells, just above the sum total, and insert it as a new row just above the sum total so another line of data can be input. But I want the sum total to sum also the newly inserted row. Presently I can't figure out how to do it. Example A B C 1 24 Oct Dollars In $100 2 25 Oct Dollars out $25 3 Total ...

How to make chart columns thicker
Hi I am using excel 2007. I am trying to create a chart with 20 weeks in the x axis. The chart type is Column, clustered column. The chart displays two lots of data. The problem is, the columns are displayed as very thin, in fact they're little more than lines, yet there is a lot of room on the chart for them to be much thicker. How do I make the columns thicker? The chart just looks daft at the moment. Thanks for any help Colin Ok, brain in gear now, I've worked out what was wrong. The problem was that the x-axes was recognising the data as a date. I told it that it was text by ...

move cursor down after add new item
I use a custom button to add a new item to the transaction grid. After that, the cursor in the transaction grid stays in the newly added line, it doesn't move down to a new line. I searched this newsgroup tried to find a solution. Then I used the SendKeys("{DOWN}") but it still doesn't work. My code is as following: Public Function Process(ByRef MySession As Object) As Boolean Dim frm As New frmFare .... If frm.ShowDialog = Windows.Forms.DialogResult.OK Then My.Computer.Keyboard.SendKeys("{DOWN}") End If Process = Tru...

Printing Long Columns
I have a simple worksheet with order numbers in column A and corresponding completion dates in column B. Since there are over 1,000 orders, it takes many pages to print the sheet, with a great deal of paper wasted on the blank area to the right of the two columns. Other than the tedious process of cutting and pasting, is there a way to arrange the information into multiple multiple columns across the page? Try this example on David's site Charlie http://www.mvps.org/dmcritchie/excel/snakecol.htm -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Cha...

Help: Reinstall on new PC won't work
Get the dreaded Activation code doesn't work hardware has changed. I emailed the address on wednesday. No response. I am moving to quicken, but I don't want to do it until I have time. Any way i can get around this for now. Did you have original boxed CD, or a downloaded copy of Money? "Frank Spina" wrote: > Get the dreaded Activation code doesn't work hardware has changed. I > emailed the address on wednesday. No response. > > I am moving to quicken, but I don't want to do it until I have time. Any > way i can get around this for now. >...

Outlook XP
How do I get Outlook XP to play a sound for notification when new mail arrives and repeat playing the sound until it has been read?Thanks for ideas! You can't. But you can configure a longer wav-sound. Control Panel-> Sounds and Audio Devices-> tab Sounds-> New Mail Notification. You can even rip a CD to a very big wave-file so it will play the CD. I'm not sure whether you are able to stop playing it so make it a CD you really really like ;-) -- Roady www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more......

Finding Cells that Total a Value
Hello Friends, I need some assistance in solving a problem. I have a spreadsheet with over five hundred lines of transactions. The sum of these transactions are creating a balance on the account. Is there any formula/macro that will help me find the transactions creating the balance? The sum of the account should be zero. To clarify, if we owe client money, there would be a transaction setting up that postive balance then a payment on the account taking it back to zero. There could be multiple transactions and then one net payment. Or we could be due to receive. So at the end of the day, th...

Stacked Column Chart Data Label Connector Lines
I can't for the life of me figure out how to place the data labels for a stacked column chart (# or %) (1) next to the column, then (2) have lines connecting the data value to the proper block of the column. It does it automatically for pie chart labels, but not columns. Anyone have insight into these two issues? Thanks in advance. Not a solution, but explaination: XL won't let you place the labels by design to the side because its possible to place stacked columns next to each other (gap width = 0). This would then make it look like the values were associated with wrong col...

Total Items
Hi - Is there a way to get a total number of items that are about to be tendered, on the POS screen ? Thanks. You can customize the status.htm file. Not sure exactly what the variable is that you want, but I know it cn be done. "NJS" <NJS@discussions.microsoft.com> wrote in message news:538F3CC7-F23D-43C7-8A5A-4319B8688E5B@microsoft.com... > Hi - > Is there a way to get a total number of items that are about to be > tendered, > on the POS screen ? > Thanks. Jason, Send me your email I have already customized this and will forward you. "Jason&qu...

Formula #72
I want to round up the result of a formula. What can I add to the existing formula to accomplish this? if your number is in cell A1, use =round(A1,0). nikki "sdmccabe" wrote: > I want to round up the result of a formula. What can I add to the existing > formula to accomplish this? You could use the ROUNDUP formula. =ROUNDUP(your formula,0) The zero above represents how many decimal places you want to round to. HTH, Elkar "sdmccabe" wrote: > I want to round up the result of a formula. What can I add to the existing > formula to accomplish this? I...

HOW TO SORT 4 COLUMNS
I HAVE A CUSTOMER LIST THAT I NEED TO SORT IN THIS ORDER, ST (COLUMN G), CITY (F), ZIP (H) & NAME (A). RANGE IS A6:M565. THANKS. VICTOR select data sort and pick the rows you want to sort by, If you want it in VBA then use your macro recorder, but before you record the macro practice on it first, there is nothing more maddenning than having to record he macro 5 or 6 times because of forgetting something. then from the forms button select a command button and assign a macro to it I should explain that I need to sort the criteria in those column and it only allows three. Consequentl...

fill formulas and values
Not sure how to fill down formulas, with either dragging or CTRL-D,without taking along formats, colours, borders (especially) etc with them. A real pain having to re-format a sheet every time I auto-fill...surely there is an easier way to do this. Cheers! M Have you tried copying and using Edit > Paste Special > Formulas? HTH Jason Atlanta, GA >-----Original Message----- > >Not sure how to fill down formulas, with either dragging or CTRL-D,without >taking along formats, colours, borders (especially) etc with them. >A real pain having to re-format a sheet every...