# Dynamic Named Range

D

#### dhstein

I have a Defined Name of "VendorList:

=OFFSET(Inventory!\$KB\$2,0,0,SUMPRODUCT(--(Inventory!\$KB\$2:\$KB\$200<>"")),1)

I want this to be a dynamic range and to go from KB2 to the last cell with
data (KB144 at the moment). But there is a blank value in cell KB2 so the
range only goes to KB143. Any suggestions as to how to fix this? Thanks.

D

#### Don Guillett

try this where 99999 is any number larger than possible or use "zzzz" if
looking for text
=OFFSET(Inventory!\$KB\$2,0,0,SUMPRODUCT(--(Inventory!\$KB\$2:\$KB\$200<>"")),1)
=OFFSET(Inventory!\$KB\$2,0,0,match(999999,inventory!\$Kb:\$Kb),1)

D

#### dhstein

Thanks Don. The problem is that this is being used in a data validation
range - to give a drop down list. The cells in that list have a formula
which sometimes will evaluate to "". Your formula gives blanks in the drop
down for all the cells which evaluate to "". The original formula doesn't
have that problem.

T

#### T. Valko

Maybe this:

=OFFSET(Inventory!\$KB\$2,0,0,COUNTIF(Inventory!\$KB\$2:\$KB\$200,"?*"))

D

#### Dave Peterson

You could just add 1 to the sumproduct portion.
I have a Defined Name of "VendorList:

=OFFSET(Inventory!\$KB\$2,0,0,SUMPRODUCT(--(Inventory!\$KB\$2:\$KB\$200<>"")),1)

I want this to be a dynamic range and to go from KB2 to the last cell with
data (KB144 at the moment). But there is a blank value in cell KB2 so the
range only goes to KB143. Any suggestions as to how to fix this? Thanks.