MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

Posted by Chris on April 20, 2001 10:54 AM

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:


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)

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.



Posted by Aladin Akyurek on April 20, 2001 11:30 AM


At this very moment I'm trying to construct a different scheme to tackle with the original question. The reason for this is: I'm worried about the performance cost of named dynamic ranges the scheme I proposed depends on. Give me a bit time to check out the new one I'm trying to build.



Posted by Aladin Akyurek on April 20, 2001 2:22 PM

Chris -- New scheme...

is underway to you. Essentially, the new scheme does no longer use named dynamic ranges defined with OFFSET.

Instead, it uses, what I call, locally calculated dynamic ranges. These behave almost like ordinary named ranges.


PS. It includes also the answer to your most recent question.