Dynamic Range (again)

D

Don

All,
I am lost on this (Excel 2003) one. I have two different dynamic ranges I
use for validation. The first is a text list of various items. This seems to
work fine, but the second, is simply a range of numeric quantities (eg:
1-100) with the ability to add more.

While first list does NOT display blanks at the bottom of the dropdown, the
numeric one ALWAYS seems to do so and starts at the bottom.
I've read the contextures article - but do not see what I'm doing wrong
several suggestions said to delete blank cells in the range, but I have done
this to the bottom of the sheet.

Here are the formulas I use for the dynamic ranges. I should add that both
ranges have a header in row 1.
The text based list:
=OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterContentsList!$A:$A),1)

and the Numeric one:
=OFFSET(MasterContentsList!$E$2,0,0,COUNTA(MasterContentsList!$E:$E),1)
 
P

Peo Sjoblom

Maybe you have invisible characters or formulas showing blanks in the number
column
Since you want numbers you can change the COUNTA to COUNT which will ignore
text like "" or spaces etc



--


Regards,


Peo Sjoblom
 
D

Don

I don't how that escaped me as not even being tried yet but changing to COUNT
worked great!

The thing I could think made the difference previously is that created the
number
list using CNTRL-drag to copy them down? otherwise no idea, as I know I
deleted to the end of the sheet.

Thanks again gents!
 
P

Peo Sjoblom

It should only make a difference if your cells are not empty
if they have formulas or blanks

--


Regards,


Peo Sjoblom
 
D

Don

I am lost again. Now my text list does the same thing! I have deleted to the
bottom of the sheet. Admittedly, though when I look at the range in the
defined names list, it highlights the one blank at the bottom of the range?
But I know there is nothing there!
 

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

Similar Threads

totally lost - named range and vlookup 13
Dynamic range names, multiple criteria, sumproduct 1
Dynamic Drop Down List 4
Dependent drop down dynamic range formula, off sheet. 7
Multiple Dynamic Named Ranges across Columns 3
Is it possible to create a dynamic range in CONTROL TOOLBOX (form) 7
Dynmic Range in pivot table 1
Drop Down with dynamic named range offsheet source list 14

Top