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)
=OFFSET(Inventory!$KB$2,0,0,match(999999,inventory !$Kb:$Kb),1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dhstein" wrote in message
...

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.

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.

"Don Guillett" wrote:

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)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dhstein" wrote in message
...

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.

Maybe this:

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

--
Biff
Microsoft Excel MVP


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

"Don Guillett" wrote:

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)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dhstein" wrote in message
...

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.

You could just add 1 to the sumproduct portion.

dhstein wrote:

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.

--

Dave Peterson