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?

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?