OM,

Thanks for your response. As I worked through this problem, I was
thinking there must be a better way. Although I have it working now with
Joel's help, I will definitely try your method. Thanks.

David


"OM" wrote:


"dhstein" wrote:
I have a Cell that gets a drop down list from a range. The range of data
will change, so I'm trying to dynamically set up the drop down from a macro.
I will use cell KB1 (excel 2007) to provide the range that the drop down
will use. As new vendors are added KB1 will be modified. At this point in
the testing, I'm just trying to get the value in KB1 to be used in the macro.
The macro is shown below. I commented out the original code and also my
failed attempts to get this to work. I believe I need in cell KB1 the exact
string:

"=$KB$2:$KB$118"

Maybe I'm reading the problem wrong, but why don't you create a
dynamic name for the range, and use that for the list? If you type
=OFFSET($KB$1,0,0,COUNTA($KB:$KB),1)
as a new name, and call it (for instance) DDList, then if you use
DDList as the source for your drop down list, it will automatically
change as new items are added to the bottom of KB. Done this many
times, and seems to work well for me (if, as I say, I'm reading the
problem right....)

OM