0

I have looked everywhere for an answer to this. I want to create a drop down in Excel where 'Item 1a' must be chosen before Item 1b; Item 2a before Item 2b; Item 2b before Item 2c, etc. I want to be able to choose Item 1a, Item 2a, Item 3a, etc. in any order, but until the 'a' is chosen you can't choose the 'b'.

Each 'Item Set' is internally dependent, but externally independent.

For Example: You could choose Item 1a first, then Item 2a, then Item 2b, then Item 3a, then Item 2c, finally Item 1b. But you could not choose Item 1a, then Item 2b, because the prerequisite for choosing Item 2b is to choose Item 2a before choosing it.

Is this possible in Excel or am I cursed to creating endless, complicated drop downs?

Ryan Friend
  • 3
  • 1
  • Yes, you can achieve this using VBA (disabling or even hiding other drop-down boxes before the first one has been selected). But maybe something else might be more convenient: http://stackoverflow.com/questions/37572688/auto-complete-with-only-text-and-not-numbers-combobox-excel-vba/37620203#37620203 – Ralph Jun 30 '16 at 23:12
  • To clarifty: I am not looking at disabling dropdowns before the first one is used. I am actually wanting all the options to be in one dropdown (there will actually be several duplicates of the dropdown which will hide previously chosen options in previous dropdowns). The actual issue is that I want to create one masterlist on another sheet instead of many. I want to code, for example, the 'Item 1b' cell with an "if ... Item 1a has been chosen ... then this option is available. If ... Item 1a has not been chosen ... then this option is not available" – Ryan Friend Jun 30 '16 at 23:30

1 Answers1

1

So I think what you want is multiple dropdowns, with a dynamic range depending on previous data.

This can be done without excel VBA :)

How I would do it is a dynamic Named Range, in a hidden worksheet.

I would put the list of options like I have in A1:C5 and name however many ranges like I have in A9:A11, B9:B11 etc

Overview

Then Name the ranges whatever you want... I used dropdown 1,2,3,4,5 etc

Name the Ranges

Create some validations based upon your names

Validations

And all you have left to do is to create some formulas, using match and offset. I was a little silly in layout, preventing a fill.

Offset

I hope this helps Bryan

Bryan Davies
  • 430
  • 1
  • 3
  • 13