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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top