Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Aray Formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Aray Formula
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" wrote in message ... Hi 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Aray Formula
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" wrote in messag .. H I created a workbook with 6 worksheets, each containing about 100 name 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Formula Not Always Calculating | Excel Discussion (Misc queries) | |||
Counting contents in aray | Excel Discussion (Misc queries) | |||
VLOOKUP Question - Can I Keep the Same Aray with Drag? | Excel Worksheet Functions | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) |