Beginner question for months and subtotals

I am new to Excel (have limited exp with Word vba). I would like t
enter a list of data where column 1 shows a cost item on the lef
followed by the 12 months of the year.  Row 1  will be for net, gross 
tax, date inputs. Every month should have a subtotal. The plan is t
make a master sheet which I can use again for the next year.

I'm able to do this manually by creating an extra row in th
appropriate month and then at the end of the month make my subtotal
using the autosum icon, etc.

The problem is that I never know how many costs are coming for th
month and so for the master sheet I don't know where to put rows fo
subtotals or whether it's best to make these only when I need the
(i.e. when the month is finished).

Another problem is when I have created an autosum area (for the months
in Excel and I add an extra row (because I have another cost item).
The autosum area will no longer include the last row (so I will need t
do the autosum manually or in a realtime macro to take into account th
actual rows entered for the month).

The question is: 

Is this simple process best left without automation?

Or could I insert vba buttons into the sheet (to create rows or mak
subtotals)?

Or could I do create something with prompts for the user, e.g. th
sheet would remember the last entry and go to the right place, etc) ?

Thx for any tips

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

0
1/20/2004 5:59:28 AM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
343 Views

Similar Articles

[PageSpeed] 33

Paul
When you insert cells after the end of a range, Excel doesn't know to include that data in the range.  If you insert cells within the range Excel will automatically update that range in any formulas.  If you include an empty row, or column, in your SUM range and insert any new data in front of this empty cell, the formulas will update automatically

If you want to prevent the blank cell from being used you could use Data>Validation with Allow: Custom and then leave Formula empty

Good Luck
Mark Graesse
mark_graesser@yahoo.co
    
     ----- Paul Cox > wrote: ----
    
     I am new to Excel (have limited exp with Word vba). I would like t
     enter a list of data where column 1 shows a cost item on the lef
     followed by the 12 months of the year.  Row 1  will be for net, gross 
     tax, date inputs. Every month should have a subtotal. The plan is t
     make a master sheet which I can use again for the next year
    
     I'm able to do this manually by creating an extra row in th
     appropriate month and then at the end of the month make my subtotal
     using the autosum icon, etc
    
     The problem is that I never know how many costs are coming for th
     month and so for the master sheet I don't know where to put rows fo
     subtotals or whether it's best to make these only when I need the
     (i.e. when the month is finished)
    
     Another problem is when I have created an autosum area (for the months
     in Excel and I add an extra row (because I have another cost item).
     The autosum area will no longer include the last row (so I will need t
     do the autosum manually or in a realtime macro to take into account th
     actual rows entered for the month)
    
     The question is:
    
     Is this simple process best left without automation
    
     Or could I insert vba buttons into the sheet (to create rows or mak
     subtotals)
    
     Or could I do create something with prompts for the user, e.g. th
     sheet would remember the last entry and go to the right place, etc) 
    
     Thx for any tips
    
    
     --
     Message posted from http://www.ExcelForum.com
    
     
0
anonymous (74722)
1/20/2004 2:26:06 PM
Reply:

Similar Artilces:

Calculate 1st of month date from existing date.
I want Excel to post a starting date which is 45 days (or other time period) from an initial date. The rules are: (1) If the date is under 15 (middle of the month) set the starting date to the 1st of the month as calculated and (2) if the date is 15 or over, set starting date to the 1st of the next month. For instance, if my starting date is January 1st, 2006 and I want the date 45 days from there, the answer is February 15, 2006. Reset the calculated value of February 15, 2006 to February 1, 2006. If the date was February 16, 2006 (46 days after start), set the date to March 1, 2006. Th...

Set a recurring task ie. a task to check a website each month
It would be advantageous to be able to set a recurring task ie. a daily, weekly, monthly or other multiple task. An example would be if you needed to go to an accounts website or prospects website once a month to look for leads. Or to check out a competitor for new things on their website. It could also apply to phoning or mailing etc. This would save constantly having to remember to create a followup or write up to dozens of followups at different dates. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote f...

Report question #2
Hi is there a way on the sales reports to combine the items together, so instead of this Store 1 Food Pop 1coke Can of Coke 1 0.99 0.99 Store 1 Food Pop 1coke Can of Coke 1 0.99 0.99 Store 1 Food Pop 1coke Can of Coke 1 0.99 0.99 Store 1 Food Pop 1coke Can of Coke 1 0.99 0.99 Store 1 Food Pop 1coke Can of Coke 1 0.99 0.99 Store 1 Food Pop 1coke Can of Coke 1 0.99 0.99 You could have i...

Questions about Scheduled Bills
I am having trouble understanding the exact relationship between scheduled bills and other transactions. Most of the source of my confusion is related to a couple of situations I've encountered with Mortgage payments. When I used the initial setup wizard to configure my accounts in Money, it categorized my mortgage payments as "Bills: Mortgage Payment". Of course, I want the transaction to be entered as "Loan Payment: <accountname>". My first question is should I just change the category in the scheduled bill? My second question is related but more complex. ...

X400 requirement question
Hi We have recently migrated from Exchange 5.5 to Exch 2000 and now almost 95% to Exchange 2003. Do we need X400 email addresses in our emails. If not then how can I remove them from our reciepient policies. Regards Ahsan Ahsan Jalil wrote: > Hi > > We have recently migrated from Exchange 5.5 to Exch 2000 and now almost 95% > to Exchange 2003. Do we need X400 email addresses in our emails. If not then > how can I remove them from our reciepient policies. The X.400 email addreses are required for Exchange functionality (exchange internal mail routing is done using X.400 ...

Copy and Paste question
I have a macro that copies and pastes a large amount of data. After it has run I get the question: " There is a large amount of information on the clipboard. Do you want to [keep it]?" Could someone please tell me how I can avoid this question being asked as I never wish to keep the data? Many thanks Insert this line after the pasting is done, it will clear the clipboard: Application.CutCopyMode = False hth knut egil "Richard" <rgarwell@jaguar.invalid> skrev i melding news:bpi1ee$cf71@eccws12.dearborn.ford.com... > I have a macro that copies and pastes a l...

Validation Question #2
If I make a selection from the drop down box on sheet 1. Can that information appear in a cell on sheet 2, within the same workbook. Hi you could use a cell reference such as ='sheet1'!A1 on your second sheet -- Regards Frank Kabel Frankfurt, Germany "Jim" <Jim@discussions.microsoft.com> schrieb im Newsbeitrag news:ADB336B2-8BE7-4BA1-AA73-0E1F95A04024@microsoft.com... > If I make a selection from the drop down box on sheet 1. Can that > information appear in a cell on sheet 2, within the same workbook. ...

disc date for next month
We want to be able to choose a discount date for the next month the discount is allowed. Our terms are 2% 10th net 15th. We can choose the due date for the 15th of the next month, but we can't choose the discount date as the 10th of the next month. All the invoices entered into the system from the 1st thru the 10th calculate the discount for the current month instead of the next month. ---------------- 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 me...

records before 6 months
Hello everyone, Anyone know how to select records before 6 months? Basically I have a date field and I need to select records older than 6 months from the current date. So today is 03/29/10. I would need all the records BEFORE 10/29/09. Let me know what you guys think. Thanks, Himansu On Mar 29, 2:24=A0pm, "Himansu" <himansu...@hotmail.com> wrote: > Hello everyone, > > Anyone know how to select records before 6 months? > Basically I have a date field and I need to select records > older than 6 months from the current date. =A0So today is...

QUESTION: Styles Not Retaining Format
Hi... I've made a small phone directory... booklet-style. I've made 8 "Styles"... first 4 and second 4 are identical except for indenting and color... PROBLEM is the second 4 are different from the first 4 by "Line Spacing", "Before Paragraphs" are set to ZERO. I set the spacing to ZERO and (even immediately) save the PUB... no errors warnings... but when I re-open the PUB... the "Before Paragraphs" is set to FOUR???? Anyone have any ideas?? Thanks in advance!! Steve ...

Restore Exchange question
I'm running Exchange 2003 on a Windows 2003 Server. I've setup another network with same domain/AD running Windows 2003 server and installed Exchange 2003 server as well. I restored the exchange store db into this new exchange 2003 on the new network. The restore was successful. However because I didn't restore the windows AD state, I didn't had all the users in the AD. I noticed the mailboxes were not mapped because there wasn't any user in AD. I added few users with same login into the new AD. My question is is there a way to map the mailboxes I restored to the users in...

Odd restore question
Is it possible to backup the information store of a (2003) server and restore it to a new server in a new organization? This is a situation where the server is not validating users due to AD troubles (see "GAL? What GAL?"), and stored e-mails cannot be exported using Outlook or ExMerge; I need to rebuild the whole organization from scratch (the server is not enough, AD is totally screwed), but the users don't want to lose archived e-mails. Massimo On Thu, 15 Sep 2005 21:06:55 +0200, "Massimo" <barone@mclink.it> wrote: >Is it possible to backup the info...

Trust Accounting and a few questions
This is not so much a technical question but I was hoping someone here could answer it. Does GP have the abililty to handle trust accounts. Also, can it bring on black checks...i.e. completely blank checks with no bank numbers or check numbers? Thanks! Dave Tegarding Blank Checks - we use Mekorma to print checks. We start with completely blank check stock and Mekorma prints all the info including the bank MICR. This allows us to have one check stock for lots of accounts. "Dave" wrote: > This is not so much a technical question but I was hoping someone here could &...

Publisher 2003 question #2
I've just installed 2003 where did the red guide lines go? I used to = have both blue and red guide lines in 2002 now I only have the one blue = line (around the page) can I get the red lines back? Tricia After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Tricia <Tricia@home.vic.au>... > I've just installed 2003 where did the red guide lines go? I used to > have both blue and red guide lines in 2002 now I only have the one > blue line (around the page) can I get the red lines back? Microsoft decided that nobody used the pink guides around th...

a question 'bout const (design)
hello;-) i've a little complex control-class with it's own worker-tread for controls of that type. most control-setter-functions (for example: set the background color, let an element blink, etc...) work like this: 1. someone calls a public fn. 2. public fn calls private static threadpost-wrapper. 3. threadpost-wrapper post ThreadMsg 4. control class worker UI thread does something stupid... so, the question is: the public fn that calls the wrapper actually *could* be marked const as is just calls static and const fn. On the ohter hand, ive read somewhere that i should try to have ...

office 2003 install question
I just purchased a new HP laptop that does not have an optical drive. I have Office 2003 with my product key and want to know if there is any way to install this on my new laptop without an optical drive. Any help is appreciated. Thanks! "freddy" <freddy@discussions.microsoft.com> wrote in message news:8B1912A3-B7DB-4585-A902-543F04317242@microsoft.com... > I just purchased a new HP laptop that does not have an optical drive. I > have > Office 2003 with my product key and want to know if there is any way to > install this on my new laptop withou...

question abt location of inbox messages
Hi all, Is there a way to save messages from my: 1.inbox 2.sent items 3.del items 4. drafts to a folder on my network instead of my computer ? thanks in advance "Jetzza" <sales@jetzza.sr> wrote in message news:eU%23tW6sxKHA.984@TK2MSFTNGP05.phx.gbl... > Hi all, > > Is there a way to save messages from my: > > 1.inbox > 2.sent items > 3.del items > 4. drafts > > to a folder on my network instead of my computer ? Of course, you can save them anywhere that you want to. If you are asking about moving the 'stor...

A simple Question
This may seem very basic but I am going to ask the question anyway :) I have CRTM setup and working on sbs2003 and runs fine and we are slowly learning lots about it. I have no problem creating leads, accounts, contacts etc etc However I receive a large amount of emails which I want to create as leads. I can do this but it seems a long way round the way we do it as we have to fill in the details from that email. Is there a simple way of once the email is received simply clicking somewhere in outlook 2007 or anywhere else that simply creates the email immediateley as a lead with the i...

Mailbox size alerts
Hi, I've got a couple of issues that my MD has asked me to look into, wondered whether anyone on here could help me out :-) Is it possible to send the Mailbox Size Alert message to someone else as well as the mailbox owner? (in this instance I need to send a message to the owner of the mailbox AND their immediate superior) Also is it possible to issue warnings based on the number of e-mails as well as the size? My MD seems to think that anyone with a large amount of e-mails isn't managing their workload correctly! BTW I am running Exchange2003 on Windows2003 Server. Thanks in adva...

Simple query question 08-05-10
Hi, I'm using SQL Server 2005. What I want is to return a column "venue", but if that column is null, I want to return the value of the column "site" (in either case, I want the column header to be "venue"). How do I construct such a query? Thanks, - Dave You can use expression with COALESCE and alias the expression as venue: SELECT COALESCE(venue, site) AS venue... -- Plamen Ratchev http://www.SQLStudio.com Try this: SELECT COALSECE(venue, site) AS venue FROM my_table -- Gert-Jan laredotornado wrote: > > Hi, &...

problem posting questions
test any problems posting to this list today? i have tried twice and it is not working. thanks. Mike Better use a Newsreader like OE. Read this from Dave Peterson '*************************************************** You may want to connect to the MS newsgroups directly. Chip Pearson has some notes written by Leonard Meads at: http://www.cpearson.com/excel/DirectConnect.htm David McRitchie's notes at: http://www.mvps.org/dmcritchie/excel/xlnews.htm http://www.mvps.org/dmcritchie/excel/oe6.htm Tushar Mehta's notes at: http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.ht...

Average starting with first month
I have a large worksheet of sales data where products are down the rows and months are across columns. I would like a formula to calculate the average monthly sales but only inlude those months starting with the month of first sale going forward. If a sale month is zero the cell is blank Example worksheet: Mo1 / Mo2 / Mo3 / Mo4 / Mo 5 Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months) Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of zero) Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5 of zero) ...

Track in CRM question
Can somebody explain to me how the Track in CRM functionality works? Our users have the "e-mail messages that contain a CRM tracking token" selected as a tracking option. We don't have the router installed on the exchange server and on the server side in system settings we have the "Track e-mail" option set to "No" on the E-mail tracking tab. Should we set the "Exclude e-mail" between CRM users to Yes or No? One of our users had mistakenly pushed the "Track in CRM" button for one of her personal emails (I think this is the case). No...

Outlook 2003 copying messages to folders question
I am using Outlook 2003 and have a question about filing either “incoming” or “sent” mail in any of the folders that I have created. The filing choices in mail are either to “move” or “copy”. If I choose to “copy”, Outlook will create a copy of the email and place it in the folder I have selected. If, for some reason, I forget that I’ve already filed the email in that same folder and do it again, Outlook doesn’t inform me that a copy of that email already exists in the file. As such, it is very possible to have multiple copies of the same email filed in a folder. I would lik...

Not a Vista Question, but...
I have used news forums such as this for years - dating all the way back to Windows 95. I have recently noticed that my favorite Microsoft Word -- and other software specific forums -- are no longer being supported by Microsoft. They contained some brilliant MVP professionals. Has this talent disbanded altogether, or have they re-grouped elsewhere under another banner? Thanks in advance. Gordon Biggar Houston, Texas Microsoft is in the process of killing off its newsgroups. They will = all be killed by October 1 of this year. Its alternative is a web-based = forum ...