Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
Hi everyone...My name is Traci..........I have a question regarding excel if
someone could help I would greatly appreciate it... I started a new job about 2 months ago and we are using excel...The page has been set up into columns for; address, file number, fee amount, and then the GST section which gets added in automatically, and then who the job is going to....it has been set up this way for about 4 years. We are using the same page on excel as 4 years ago so you can scroll up and get all the info from 2002, etc....My question is the receivable total at the bottom shows all the combined totals owing from all the years so it shows a HUGE amount owing to us for all those years combined....How do I separate each year to provide a different amount for receivables for each year.....Do I just add in extra spaces to separate the years or how would I go about separating the yearly totals? Please help me as I really need to get this done ASAP....PLEASE help if you can...Thank you very much from someone desparatly needing assistance....lol |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
Excel will add together what you tell it to! If you click in the cell that
contains the total, you should see a formula above; that formula likely contains a range that includes previous years. What you could do is click in that cell, click your AutoSum button and at first Excel will "guess" what you want it to add. If the range isn't correct, click and hold down on the mouse to highlight all the cells you want it to add instead. "Keepitsimple72" wrote: Hi everyone...My name is Traci..........I have a question regarding excel if someone could help I would greatly appreciate it... I started a new job about 2 months ago and we are using excel...The page has been set up into columns for; address, file number, fee amount, and then the GST section which gets added in automatically, and then who the job is going to....it has been set up this way for about 4 years. We are using the same page on excel as 4 years ago so you can scroll up and get all the info from 2002, etc....My question is the receivable total at the bottom shows all the combined totals owing from all the years so it shows a HUGE amount owing to us for all those years combined....How do I separate each year to provide a different amount for receivables for each year.....Do I just add in extra spaces to separate the years or how would I go about separating the yearly totals? Please help me as I really need to get this done ASAP....PLEASE help if you can...Thank you very much from someone desparatly needing assistance....lol |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
Let's assume that one of your columns has the date in it (for example column
Z). In an unused column enter=YEAR(Z2) and copy down. Put a label in Z1. Go to Z1 and switch on AutoFilter. You can now select only those rows for a given year. In place of the SUM() function for the receivables, use the SUBTOTAL() function. See Excel help. SUBTOTAL can be used to sum up only the visible cells and thus display yearly totals if the other years are filtered out. -- Gary's Student gsnu200708 "Keepitsimple72" wrote: Hi everyone...My name is Traci..........I have a question regarding excel if someone could help I would greatly appreciate it... I started a new job about 2 months ago and we are using excel...The page has been set up into columns for; address, file number, fee amount, and then the GST section which gets added in automatically, and then who the job is going to....it has been set up this way for about 4 years. We are using the same page on excel as 4 years ago so you can scroll up and get all the info from 2002, etc....My question is the receivable total at the bottom shows all the combined totals owing from all the years so it shows a HUGE amount owing to us for all those years combined....How do I separate each year to provide a different amount for receivables for each year.....Do I just add in extra spaces to separate the years or how would I go about separating the yearly totals? Please help me as I really need to get this done ASAP....PLEASE help if you can...Thank you very much from someone desparatly needing assistance....lol |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
I think a Pivot Table may be what you need.
See Debra Dalgleish's site for excellent assistance with PT's http://www.contextures.on.ca/tiptech.html On Wed, 28 Feb 2007 10:42:08 -0800, Keepitsimple72 wrote: Hi everyone...My name is Traci..........I have a question regarding excel if someone could help I would greatly appreciate it... I started a new job about 2 months ago and we are using excel...The page has been set up into columns for; address, file number, fee amount, and then the GST section which gets added in automatically, and then who the job is going to....it has been set up this way for about 4 years. We are using the same page on excel as 4 years ago so you can scroll up and get all the info from 2002, etc....My question is the receivable total at the bottom shows all the combined totals owing from all the years so it shows a HUGE amount owing to us for all those years combined....How do I separate each year to provide a different amount for receivables for each year.....Do I just add in extra spaces to separate the years or how would I go about separating the yearly totals? Please help me as I really need to get this done ASAP....PLEASE help if you can...Thank you very much from someone desparatly needing assistance....lol |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
Thanks Lyn....;o)
I will try that and hopefully it works...I really appreciate your help "Lyn" wrote: Excel will add together what you tell it to! If you click in the cell that contains the total, you should see a formula above; that formula likely contains a range that includes previous years. What you could do is click in that cell, click your AutoSum button and at first Excel will "guess" what you want it to add. If the range isn't correct, click and hold down on the mouse to highlight all the cells you want it to add instead. "Keepitsimple72" wrote: Hi everyone...My name is Traci..........I have a question regarding excel if someone could help I would greatly appreciate it... I started a new job about 2 months ago and we are using excel...The page has been set up into columns for; address, file number, fee amount, and then the GST section which gets added in automatically, and then who the job is going to....it has been set up this way for about 4 years. We are using the same page on excel as 4 years ago so you can scroll up and get all the info from 2002, etc....My question is the receivable total at the bottom shows all the combined totals owing from all the years so it shows a HUGE amount owing to us for all those years combined....How do I separate each year to provide a different amount for receivables for each year.....Do I just add in extra spaces to separate the years or how would I go about separating the yearly totals? Please help me as I really need to get this done ASAP....PLEASE help if you can...Thank you very much from someone desparatly needing assistance....lol |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
Thanks for your quick reply...I will see if that helps and hopefully I will not get stuck again...;o) Thank you "Gary''s Student" wrote: Let's assume that one of your columns has the date in it (for example column Z). In an unused column enter=YEAR(Z2) and copy down. Put a label in Z1. Go to Z1 and switch on AutoFilter. You can now select only those rows for a given year. In place of the SUM() function for the receivables, use the SUBTOTAL() function. See Excel help. SUBTOTAL can be used to sum up only the visible cells and thus display yearly totals if the other years are filtered out. -- Gary's Student gsnu200708 "Keepitsimple72" wrote: Hi everyone...My name is Traci..........I have a question regarding excel if someone could help I would greatly appreciate it... I started a new job about 2 months ago and we are using excel...The page has been set up into columns for; address, file number, fee amount, and then the GST section which gets added in automatically, and then who the job is going to....it has been set up this way for about 4 years. We are using the same page on excel as 4 years ago so you can scroll up and get all the info from 2002, etc....My question is the receivable total at the bottom shows all the combined totals owing from all the years so it shows a HUGE amount owing to us for all those years combined....How do I separate each year to provide a different amount for receivables for each year.....Do I just add in extra spaces to separate the years or how would I go about separating the yearly totals? Please help me as I really need to get this done ASAP....PLEASE help if you can...Thank you very much from someone desparatly needing assistance....lol |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
Thanks Gord....I will check out the site and see if there are some easy options for my dilema....lol Have a great day.... "Gord Dibben" wrote: I think a Pivot Table may be what you need. See Debra Dalgleish's site for excellent assistance with PT's http://www.contextures.on.ca/tiptech.html On Wed, 28 Feb 2007 10:42:08 -0800, Keepitsimple72 wrote: Hi everyone...My name is Traci..........I have a question regarding excel if someone could help I would greatly appreciate it... I started a new job about 2 months ago and we are using excel...The page has been set up into columns for; address, file number, fee amount, and then the GST section which gets added in automatically, and then who the job is going to....it has been set up this way for about 4 years. We are using the same page on excel as 4 years ago so you can scroll up and get all the info from 2002, etc....My question is the receivable total at the bottom shows all the combined totals owing from all the years so it shows a HUGE amount owing to us for all those years combined....How do I separate each year to provide a different amount for receivables for each year.....Do I just add in extra spaces to separate the years or how would I go about separating the yearly totals? Please help me as I really need to get this done ASAP....PLEASE help if you can...Thank you very much from someone desparatly needing assistance....lol |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|