Hi Maria,

see this page for info on dynamic ranges
http://www.decisionmodels.com/optspeedf.htm

the formula for the dynamic range would be something like this
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

but if you want a separate dynamic range for each year you could need to
replace the anchor cell reference Sheet1!$A$1 with a formula that gets the
start row of the year, and the count would need to count only rows for that
year.

If you have a column that contains the year number in the first row for that
year you can find the row number using MATCH, something like
=Match("2005",$A$1:$A$200,0) (this will find the first row containing 2005)

so if you have four of these formulae in 4 cells somewhere you have the
start row number for each year and the number of rows for that year is
startyear2-startyear1 and so on.

So then you can construct a dynamic range formula something like this which
should return only the rows in the year
=OFFSET(Sheet1!$A$1,startyear1-1,0,startyear2-startyear1,1)

This says something like :
start in A1
then Offset down to the start of year 1
then return the number of rows there are in that year



Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

"Maria J-son" wrote in message
...
Hi again Charles,

I have read your pages and think that I maybe actually can make the
formulas faster. In Sheet1 I have the named ranges NameRngA15A200 and
NameRngF15F200 (range as written in the name)where you can add and delete
rows and input data up to 200 rows - Usually ONLY maybe 10 of these rows
have data (but sometimes with empty rows between).
There are like 8 columns of these named ranges, all checking what year it
is (could be 4 different years= 2005, 2006, 2007 or 2008)

In Sheet2 I have this array formula 200 rows down and 10 similar columns ,
the 9 to the right just linked in a array to the left column that contain
this array:

=IF(ISERR(INDEX(NameRngF15F200;SMALL(IF(NameRngA15 A200="2005";ROW(INDIRECT("1:"&ROWS(NameRngA15A200) )));ROW(INDIRECT("1:"&ROWS(NameRngA15A200))));1)); "";INDEX(NameRngF15F200;SMALL(IF(NameRngA15A200="2 005";ROW(INDIRECT("1:"&ROWS(NameRngA15A200))));ROW (INDIRECT("1:"&ROWS(NameRngA15A2001))

I don't use advanced filer bacause I have four of these arrays , starting
around C207,C410,C613 and C816 with some headlines and sums between. The
arrays are the same, but with different years.

I have understood that i should use OFFSET formulas as names and use
COUNTA to know how many. Since there could occure empty lines in Sheet1, I
used INDEX in this array to get them without these empty lines.

I have problem to interprit your advices on the webbsite to this
particular problem. How can I use OFFSET/COUNTA in this? How can I reduce
the number of lines in the arrays in Sheet2 when I don't know how many
rows there is used in Sheet? The output of four different years will be of
different number of rows, dynamically.

It would be very, very, very nice of you, if I could get some advices from
you (or any other of cource) regarding this array formula. The input in
sheet1 will be used very much and therefore it is critical that the
calculation will take to long time.

/ Kindest regards



"Charles Williams" skrev i meddelandet
...
- if calculation takes 4 seconds then you need to switch to Manual
(Tools--Options--calculation) and press F9 whenever you want to
calculate

- if your array formulae are taking the calculation time (which would not
be surprising), why not take a look at speeding them up? see
http://www.decisionmodels.com/optspeedj.htm for some suggestions

- if you really want to control calculation of specific ranges use
Range.calculate but be wary of its quirks (which vary by Excel version)
see http://www.decisionmodels.com/calcsecretsg.htm for details. Not sure
this is the right solution for you.

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

"Maria J-son" wrote in message
...
Hi,

I take this from another thread since it developed to another direction.

I have a lot of links and after a cell value is changed, it take far to
many seconds afterwards for the calculation to be possible to use it. Is
it possible to:

A/ Inhibit the common Application.Calculate that run after every change?
B/ Just calculate specific ranges in specific sheets after a
worksheet_change?
C/ Be sure to cover every aspect to swicht it on again when leaving the
workbook, and then continue with "the inhibited way" when returning to
the workbook?

Please answer any of these, or all if possible.

/Kind regards