Dynamic Named Range

[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.

 

[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)

 

[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. Valko]

Maybe this:

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

 

[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.