Live Chat & Podcast at 1:00PM Eastern on Sunday!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash desktop drive driver drivers error ethernet excel freeze gaming hard drive hardware hdmi internet laptop malware memory missing monitor motherboard network printer problem ram random registry router slow software sound trojan ubuntu 11.10 uninstall usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
YTD Totals on Excel Spreadsheet

Reply  
Thread Tools
loucrew8's Avatar
Computer Specs
Junior Member with 4 posts.
 
Join Date: Feb 2010
Experience: Intermediate
08-Feb-2010, 02:47 PM #1
Lightbulb YTD Totals on Excel Spreadsheet
I have created a spreadsheet to track monthly financials


My problem is I want a YTD column for revenues and expenses to automatically calculate when I enter the new month totals...I have set up a column (d9) for monthly revenue and a column (e9) for the YTD expense...I know it would be easier to create a column for each month but I already have a report that gives me that detail if I need it...this report is really for each employee to see each month how their budget line items are doing...I was told this is doable but I would need to write a macro and that is where I really need the help...Have no idea where to begin

Thanks,
p
etaf's Avatar
Computer Specs
Moderator with 34,408 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
08-Feb-2010, 06:25 PM #2
can you load an example with dummy data

not sure where you want the summary YTD in a seperate sheet or on the same sheet - or where each month would be entered

D9 and E9 doesnt help much

whats in A, B, C

where a the 12 months of the year ?
__________________
Note: I have very limited time during weekdays to visit here, so there will be a delay in replying
UK timezone
Please let us know what the final solution was to any problem posted
Pedro15's Avatar
Member with 256 posts.
 
Join Date: Oct 2008
Experience: Intermediate
08-Feb-2010, 07:33 PM #3
following on from above do you want individual totals for each expense/income as well as a grand total for each?

Pedro
Pedro15's Avatar
Member with 256 posts.
 
Join Date: Oct 2008
Experience: Intermediate
08-Feb-2010, 08:51 PM #4
The attached rough spreadsheet may assist.

Pedro
Attached Files
File Type: xls YTD Income Expenses.xls (24.5 KB, 156 views)
nesr's Avatar
Computer Specs
Member with 50 posts.
 
Join Date: Nov 2008
Experience: Advanced
09-Feb-2010, 03:50 AM #5
Quote:
Originally Posted by Pedro15 View Post
The attached rough spreadsheet may assist.

Pedro
Sorry, what did loucrew8 want to do?

he want to make a summary table for each month of the year?

please loucrew8 explain more...
thanks Pedro for the example
loucrew8's Avatar
Computer Specs
Junior Member with 4 posts.
 
Join Date: Feb 2010
Experience: Intermediate
09-Feb-2010, 07:21 PM #6
YTD Totals
Here is a copy of a spreadsheet...thanks so much for any help you can give me...
p
Attached Files
File Type: xls Financial ReportsJanuary2010.xls (13.5 KB, 78 views)
Pedro15's Avatar
Member with 256 posts.
 
Join Date: Oct 2008
Experience: Intermediate
09-Feb-2010, 08:29 PM #7
Where do you get figures from for rows 3 and 4?

And what is position if say Service Sales are $1400 and Retail sales are $800.
Maximum commission base under your structure on this scenario appears to be only $700 is this to be the base or the $800 .

Pedro

Last edited by Pedro15; 10-Feb-2010 at 02:26 AM..
etaf's Avatar
Computer Specs
Moderator with 34,408 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
10-Feb-2010, 09:11 AM #8
Quote:
I know it would be easier to create a column for each month but I already have a report that gives me that detail if I need it...this report is really for each employee to see each month how their budget line items are doing.
We need to see the " Other Report" in order to work out how to populate the running total
if this sheet is saved - we would need to know the reference (drive ID - Directories and filename ) and we can update from that saved file ...
loucrew8's Avatar
Computer Specs
Junior Member with 4 posts.
 
Join Date: Feb 2010
Experience: Intermediate
10-Feb-2010, 05:32 PM #9
I just developed the spreadsheet (attachment -FinancialReportsJanaury2010.xls) for the employees to track their budget line items...the employees don't need the detail of the other reports...These reports are printed by a separate piece of financial software and are in no way linked to this spreadsheet...Once the reports are printed I manually input the expenses and the revenues...I just want to be able for the YTD columns to automatically calculate the totals for me once I do the manual input...It seems very easy in concept but in maybe its not possible but I'm not sure.

Thanks for any help
p
etaf's Avatar
Computer Specs
Moderator with 34,408 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
10-Feb-2010, 05:55 PM #10
no attachment
so you should be able to set that up in a pivot table and then refresh each time you update quite easily

Do you use the monthly data at all or just copy and paste into a spreadsheet
if so then a good facilty is to create a list then when you enter data the pivot will automatically refresh with the ! button

do you have an example of the data entry ?

If its simply the addition of columns in a different worksheet

=SUM(sheet2!A2:A500)
if the data for expenses are on sheet 2 and in cells A2 toA500
__________________
Note: I have very limited time during weekdays to visit here, so there will be a delay in replying
UK timezone
Please let us know what the final solution was to any problem posted
loucrew8's Avatar
Computer Specs
Junior Member with 4 posts.
 
Join Date: Feb 2010
Experience: Intermediate
10-Feb-2010, 06:33 PM #11
YTD Totals with attachment
Thanks so much for any help...Its sounds so simple but its causing quite the frustration....I've done a little reading on the tables you suggested but never done one before...I have attached a "dummy" spreadsheet attachment.

Thanks,
P
Attached Files
File Type: xls Financial ReportsJanuary2010.xls (13.5 KB, 47 views)
etaf's Avatar
Computer Specs
Moderator with 34,408 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
11-Feb-2010, 04:40 AM #12
that dummy is the same as the last one
We need to see the sheet where the actual value entries for expense and revenue are entered - for each month, so they can be added up

all your showing is a summary sheet

we need to put a formula into cell E and G which calculates all the months up - where is this information held ?
__________________
Note: I have very limited time during weekdays to visit here, so there will be a delay in replying
UK timezone
Please let us know what the final solution was to any problem posted
Reply

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 03:13 AM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.