Named Aray Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

H

I created a workbook with 6 worksheets, each containing about 100 named ranges (the ranges are for months of the year (Jan to Dec) with the cells referencing the days in each months). I use the named ranges in array formulas (daily cost and chemical usage calcualtiosn per month). I have it setup for 2003, now I created exactly the same one for 2004, but February has 29 days in 2004 (2003 Feb has 28). Al my range references will be out by one cell from Feb onwards. How do I update all those references in one go (maybe with VBA)? Is there an easier way to do these calcualtions than using hundreds of named ranges in array formuals
 
Perhaps the following will work:

1. Insert a row right above February 28, 2004.

2. Copy February 28, 2004 into the new blank row.

3. Change the original February 28, 2004 to February 29, 2004.

This should preserve all your named ranges.

--

Vasant


Francois Benadie said:
Hi

I created a workbook with 6 worksheets, each containing about 100 named
Click to expand...
ranges (the ranges are for months of the year (Jan to Dec) with the cells
referencing the days in each months). I use the named ranges in array
formulas (daily cost and chemical usage calcualtiosn per month). I have it
setup for 2003, now I created exactly the same one for 2004, but February
has 29 days in 2004 (2003 Feb has 28). Al my range references will be out
by one cell from Feb onwards. How do I update all those references in one
go (maybe with VBA)? Is there an easier way to do these calcualtions than
using hundreds of named ranges in array formuals?
 
Yes, it will preserve my named ranges, but the financial and chemical usage calcualtiosn will then not be accurate for February - the formulas will then calcualte average monthly cost and chemcial usage on 29 days and not on 28 days

Is there not another way of doing this whithout using hundreds of arrays formulas

----- Vasant Nanavati wrote: ----

Perhaps the following will work

1. Insert a row right above February 28, 2004

2. Copy February 28, 2004 into the new blank row

3. Change the original February 28, 2004 to February 29, 2004

This should preserve all your named ranges

--

Vasan


Francois Benadie said:
H
Click to expand...
ranges (the ranges are for months of the year (Jan to Dec) with the cell
referencing the days in each months). I use the named ranges in arra
formulas (daily cost and chemical usage calcualtiosn per month). I have i
setup for 2003, now I created exactly the same one for 2004, but Februar
has 29 days in 2004 (2003 Feb has 28). Al my range references will be ou
by one cell from Feb onwards. How do I update all those references in on
go (maybe with VBA)? Is there an easier way to do these calcualtions tha
using hundreds of named ranges in array formuals
 
Back
Top