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(Sh eet1!$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

On Mar 13, 6:06*pm, "T. Valko" wrote:
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.

--
Biff
Microsoft Excel MVP

"Robert H" wrote in message

...



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.- Hide quoted text -

- Show quoted text -