dynamic dependent list

[Paul]

I have used the method described on Debra Dalgleish's web site to create
data validation in column B that is dependent on what is entered in column
A.
So, column A is validated by a list, the source of which is =list1
The name "list1" is defined by the equation
=Sheet2!$A$2:$A$21
These cells contains "item1", "item2" and so on.
Column B is validated by a list, the source of which is =INDIRECT(A1)
The name "item1" is defined by the equation
=Sheet2!$B$2:$B$21
and the name "item2" is defined by the equation
=Sheet2!$C$2:$C$21
and so on.
All of this works fine. I get a drop-down box in A1 and make a selection. I
then get a drop-down box in B1 with entries that depend on the value chosen
for A1.

However, the length of each of these lists will vary from 3 up to 20
entries, and such entries will be added and deleted as time goes by. The
result is that the drop-down list is long, perhaps containing many blanks. I
wanted to make the length dynamic so that only genuine entries are shown.
I changed the definition of the name "list1" to
=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A$2:$A$21),1)
and this works perfectly for validation in column A.
I then changed the definition of the name "item1" to
=OFFSET(Sheet2!$B$1,1,0,COUNTA(Sheet2!$B$2:$B$21),1)
and this is where I encountered a problem.
If I select "item1" in A1, no drop-down box appears in B1 when I click the
down-arrow. If I select "item2" (which is still defined by the fixed range),
a drop-down box appears as expected.

Can anyone explain this, or better still tell me how to fix the problem?
Thanks in anticipation.

 

[Dave Peterson]

I don't think indirect() and those offset() names play well with each other.

In one workbook that I did, I got rid of the offset name and just used the real
range. But I had to be careful to either insert more info in the middle of the
range or reassign the range name.

Another alternative maybe to just create the range name each time you open the
workbook:

with worksheets("sheet2")
.range("B2", .cells(.rows.count,"B").end(xlup)).name = "Item1"
.range("c2", .cells(.rows.count,"C").end(xlup)).name = "item2"
end with

Put it in your auto_open or workbook_open and it'll be updated each time the
workbook opens.

(This works ok if you are the only person who updates those lists. If the users
do it on the fly, you'll want to have this naming macro based on something
else.)

 

[Paul]

Thanks for your suggestion, Dave. Unfortunately a macro isn't an option in
this case, as the file is emailed frequently and security policies prohibit
the use of macros in emailed files. There are too many such lists to do
manually.

I'm sure you're right that it's the interplay of INDIRECT and OFFSET. I just
wish I could understand it! Thanks for your interest, anyway.