Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 367
Default 12 month total in Excel - by worksheet - need a formula

I have an Excel 2003 document where there are 12 worksheets named for each
month. Each month I add a worksheet to the document and name it the month and
then delete the oldest month.

In my worksheets there is a consistent layout and one cell there is a total
percentage of all the months. I have criteria leading up to that percentage.
I would like a formula/function/VB coding that will add M10 for just 12
months of worksheets. Below is the formula that I was using.

=((SUM(Aug08:Jun09!E13))-(SUM(Aug08:Jun09!E10:E12)))/(SUM(Aug08:Jun09!E13))*D10

I recently simplified this to named ranges
(Batch)-(Delivery)/(Batch)*Service) and tried naming the 12 worksheets to the
named range called "Sheets" so that way when I add a spreadsheet (month) or
delete one, I can have it be part of the "sheets" named range.

How do I incorporate the named range of the sheets in a funtion of some
sort? Should I be using the sumproduct function - or is there another
approach?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,722
Default 12 month total in Excel - by worksheet - need a formula

You can keep the named ranges, but need to take a slightly different
approach. As you are adding a "new" month and deleting the "old" month, we
can use boundary sheets. Place a blank worksheet before your first month of
data (presumably the oldest/newest) with the name of BeginningSheet.
Similarly, place a blank worksheet after last month of data labeled
EndingSheet. You can then hide both these worksheets from view. Now, in your
3D references, you can use references like
SUM(BeginningSheet:EndingSheet!E13)
and this will include all the sheets inbetween, regardless of you
add/deleting or changing names.

You can still gives this a defined name if you want.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jason" wrote:

I have an Excel 2003 document where there are 12 worksheets named for each
month. Each month I add a worksheet to the document and name it the month and
then delete the oldest month.

In my worksheets there is a consistent layout and one cell there is a total
percentage of all the months. I have criteria leading up to that percentage.
I would like a formula/function/VB coding that will add M10 for just 12
months of worksheets. Below is the formula that I was using.

=((SUM(Aug08:Jun09!E13))-(SUM(Aug08:Jun09!E10:E12)))/(SUM(Aug08:Jun09!E13))*D10

I recently simplified this to named ranges
(Batch)-(Delivery)/(Batch)*Service) and tried naming the 12 worksheets to the
named range called "Sheets" so that way when I add a spreadsheet (month) or
delete one, I can have it be part of the "sheets" named range.

How do I incorporate the named range of the sheets in a funtion of some
sort? Should I be using the sumproduct function - or is there another
approach?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
help calculated total for each month jengy1 Excel Worksheet Functions 4 May 13th 09 03:36 PM
formula to set for adding total by month aw Excel Discussion (Misc queries) 1 November 11th 08 10:40 AM
How to get month wise total of a worksheet having a years data? TonyK Excel Discussion (Misc queries) 1 August 26th 07 10:20 AM
averaging a total out over a month Simon Parker Excel Discussion (Misc queries) 0 April 3rd 07 02:06 PM
Create Month Timetable on a worksheet different month each works Courtney Excel Worksheet Functions 1 October 15th 06 11:48 AM


All times are GMT +1. The time now is 05:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"