Dynamic Data Validation




What I want to be able to do is to have two dropdown boxes on a worksheet so
that the selection in the first (dropdown1) governs what is shown in the
second (dropdown2).

In cell A1 I have used data validation to display the range A2:A4. This
displays "Head Office", "Branch1", & "Branch2". These are my offices. When I
select "Head Office" I want dropdown2 (in cell B1) to display range B2:B4
which has three names of employees employed at Head Office. Similarly, if
"Branch1" is selected then dropdown2 will disply C2:C4 which contains the
names of my Branch1 employees. Simple enough?

I have tried Data Validation and then selecting 'Custom' and typing in a
formula along the lines of...

IF(A1="Head Office",B2:B4,"")

but this does not work.

I tried using VBA events handlers. I tried using Worksheet_Change(ByVal as
Excel.Range) but when I change the selection in dropdown1 there is no effect.
It seems like VBA is not recognising the event. (By the way, I have used
EventEnabler = False etc).

It seems to me that my problem must have been encountered before. Any ideas?

I would like to avoid using VBA list boxes of comboboxes as they look messy
on the sheet and cannot be sized to a single excel cell.

I hope there is help at hand...





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