Dynamic Named Range

  • Thread starter Thread starter dhstein
  • Start date Start date
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.
 
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)
Click to expand...
=OFFSET(Inventory!$KB$2,0,0,match(999999,inventory!$Kb:$Kb),1)
 
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.
 
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.
Click to expand...
 
Back
Top