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


"Robert H" wrote in message
...
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

On Mar 11, 5:48 pm, "T. Valko" wrote:
One way:

Dynamicrangefrom A1 to thefirstemptycellin column A:

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

--
Biff
Microsoft Excel MVP

"Robert H" wrote in message

...



I need to creat adynamicrangethat that expands as therangeof data
grows but ignors a table of data that is a few lines below the active
table. using the countA function to check foremptycells works but
if I have the entire colum as the countrangeit checks for all non
emptycells which messes up therange. Im looking for something like
Selection.End(xlDown) in VBA that will go to thefirstemptycellso I
dont get into the next table of data.- Hide quoted text -

- Show quoted text -