dynamic range with a table below the working table

[Robert H]

I need to creat a dynamic range that that expands as the range of data
grows but ignors a table of data that is a few lines below the active
table. using the countA function to check for empty cells works but
if I have the entire colum as the count range it checks for all non
empty cells which messes up the range. Im looking for something like
Selection.End(xlDown) in VBA that will go to the first empty cell so I
dont get into the next table of data.

 

[T. Valko]

One way:

Dynamic range from A1 to the first empty cell in column A:

=A1:INDEX(A1:A100,MATCH(1,INDEX(--(A1:A100=""),,1),0)-1)

 

[Robert H]

One way:

Dynamic range from A1 to the first empty cell in column A:

=A1:INDEX(A1:A100,MATCH(1,INDEX(--(A1:A100=""),,1),0)-1)

--
Biff
Microsoft Excel MVP
Thanks Biff I'm trying it out
Robert

 

[Robert H]

Biff, thanks for the help. When I use that formula in the name I get
weird results. here is what Im trying to do.
In column b I have a vertical list of factors. right now only three,
Material, Temp and Soak Time which ocupy B2 through B4. b1 is the
header "Factors". I created a name fctrRng with the formula:
=B1:INDEX(B1:B100,MATCH(1,INDEX(--(B1:B100=""),,1),0)-1)

when I go back and look at the formula for the name its changed to:
=MP1!G3:INDEX(MP1!G102:G3,MATCH(1,INDEX(--(MP1!G102:G3=""),,1),0)-1)

Each time I go back and replace the formula and try to use the range,
the range is something different ever though nothing has changed on
the worksheet.

Im sure this is some element of using excel that I have not ran into
yet, but it looks like random data selection.

FYI, for staters Im using the name in a count function to count how
many factors are listed. COUNTA(fctrRng)

Robert

 

[T. Valko]

If you're going to use this as a named range then make the references
absolute:

=MP1!$B$2:INDEX(MP1!$B$2:$B$100,MATCH(1,INDEX(--(MP1!$B$2:$B$100=""),,1),0)-1)


--
Biff
Microsoft Excel MVP


Biff, thanks for the help. When I use that formula in the name I get
weird results. here is what Im trying to do.
In column b I have a vertical list of factors. right now only three,
Material, Temp and Soak Time which ocupy B2 through B4. b1 is the
header "Factors". I created a name fctrRng with the formula:
=B1:INDEX(B1:B100,MATCH(1,INDEX(--(B1:B100=""),,1),0)-1)

when I go back and look at the formula for the name its changed to:
=MP1!G3:INDEX(MP1!G102:G3,MATCH(1,INDEX(--(MP1!G102:G3=""),,1),0)-1)

Each time I go back and replace the formula and try to use the range,
the range is something different ever though nothing has changed on
the worksheet.

Im sure this is some element of using excel that I have not ran into
yet, but it looks like random data selection.

FYI, for staters Im using the name in a count function to count how
many factors are listed. COUNTA(fctrRng)

Robert

 

[Robert H]

Thanks Biff, that works. Hopefully Ive learned my lesson about named
ranges needing to be absolute.
Robert

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Thanks Biff, that works. Hopefully Ive learned my lesson about named
ranges needing to be absolute.
Robert

 

[Robert H]

Im back :O

Once I got the my working using your sugestion I went back and am
trying to understand the formula you provided. Im am lost on the index
that is used for the Match, lookup_array. In INDEX(--(B1:B100="") I
dont understand the --( It looks like its used as a function but I
cant find any referece to that.

 

[T. Valko]

This expression will return an array of either TRUE or FALSE:

(B1:B100="")

For example:

B1="" = FALSE
B2="" = FALSE
B3="" = FALSE
B4="" = TRUE
B5="" = TRUE
B6="" = FALSE

Since our MATCH lookup_value is 1 we need to convert those TRUE and FALSE to
numbers. The "--" does just that, it coerces TRUE to 1 and FALSE to 0:

--(B1="") = 0
--(B2="") = 0
--(B3="") = 0
--(B4="") = 1
--(B5="") = 1
--(B6="") = 0

Now our MATCH lookup_value will match the *first 1* of that array which
would be the reference at B4. So, the evalauted range would be from B1:B4
but don't forget that in the original formula we're subtracting 1 from MATCH
so in the end the evaluated range would be B1:B3.

 

[Robert H]

I made an modification to the formula so I wouldent have to remember
to set the test range, reqired in the original "$A$1:$A$100"
I replaced thhe range with a standard dynamic range formula. In the
match section I had to add one cell to the count for casses when there
is no table following the target table otherwise match would fail.

Sheet1!$A$1:INDEX(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),
1),MATCH(1,INDEX(--(OFFSET(Sheet1!$A$1,0,0,(COUNTA(Sheet1!$A:$A)+1),
1)=""),,1),0)-1)

I tested this with several number scenarios and it seems fail safe for
my application. If you see anything wrong please let me know.
Robert