Re: Defining a dynamic range ...(Aladin)

Aladin -

almost works GREAT but...

the array doesnt seem to update when I add or delete rows in the original table...

is there any way to control the reference to the filtered list so that when it grows or shrinks so does the finished list reference?

something instead of:

=IF(ROW()-ROW(E$3:E$17)+1>ROWS(A$3:A$17)-COUNTIF(A$3:A$17,""),"",....

i tried copying the formula down in more rows then necessary as "place holders" but that led to the '#NUM' error in all the output cells.

-- Chris

Re: Defining a dynamic range (Re: dynamic filtering of an array)
Chris

First some alternatives.

1) I believe you can do all the computations that you need directly on your original data.
2) Advanced Filter might be a much cheaper solution.
3) A system of formulas to do the job. Be warned it's rather heavy. I'll take up this one in what follows.

First create the following named dynamic ranges:

VALSETA: =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)
VALSETB: =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B),1)
SDATES: =OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$C:$C),1)
VALSETD: =OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D:$D),1)

I just assumed your data to be on Sheet1 in the range A2:Dn.

By the way, let me know if you don't know how to define such named dynamic ranges.

Activate Sheet2, to which the data associated Fiscal Week 1 will be showed.

In A1 enter: the date for lower limit

In A2 enter: the date for upper limit

In A3 enter: =IF(((SDATES>=$A$1)*(SDATES<=$A$2)),VALSETA,""),

then while in A3 select m [=COUNTA(SDATES)] rows down column A, go to the formula where you see this formula and array-enter it (that is, hit CONTROL+SHIFT+ENTER at the same time).

Activate A3, copy the formula to B3:C3. Replace VALSETA by VALSETB in B3, then select from B3 on m rows and array-enter the formula. Replace VALSETA by VALSETD in C3, then select from C3 on m rows and array enter the formula.

In E3 array-enter: =IF(ROW()-ROW(E$3:E$17)+1>ROWS(A$3:A$17)-COUNTIF(A$3:A$17,""),"",INDIRECT(ADDRESS(SMALL((IF(A$3:A$17<>"",ROW(A$3:A$17),ROW()+ROWS(A$3:A$17))),ROW()-ROW(E$3:E$17)+1),COLUMN(A$3:A$17))))

Copy this formula across to F3:G3 and then down to m rows.

You'll get the data of interest in a range from F to G neatly in a consecutive series of non-blank rows.

You can repeat this process for other fiscal weeks on other sheets.

Aladin

----------------------