Dependent Drop-down List

[JayReddy]

Hi All,

Some assistance please

I am trying to add a dependent drop-down list in Excel but having some difficulty. Listed below are the details that I am trying to create the drop-down lists for:

	A	B
1	Vendor Requestor	Vendor Approver
2	Country Level	1st Level Approver
3	Company Code Level	2nd Level Approver
4		3rd Level Approver

<tbody>
</tbody>

So far I have taken the following steps but I am stuck at point 4:

1. In Sheet 1 I have added a named range for A1:B1 as 'List1'.
2. I have then added additional named ranges for A1:B4 as 'Vendor_Requestor' & 'Vendor_Approver'.
3. In Sheet 2, via Data Validation, I have added a drop-down list value for 'List1' in cell A1. This works fine.
4. In cell B1 I have added the following formula via Data Validation =INDIRECT(SUBSTITUTE($A$1," ","_")) but this is not working.

Any ideas how I can correct my formula at point 4 so that I have the relevant drop-down list options available in Sheet 2 in cell B1 once the relevant value in cell A1 has been selected?

Thanks in advance for any support.

Regards, Jay

 

[L. Howard]

Just a guess, but see the red font instructions.

Howard

Now click onto Sheet2 and select cell A1. Go to Data>Validation, choose the "List" option then type: =List1 in the Source box. Ensure "In-cell dropdown" is checked and click Ok. Now select cell A2 and again go to Data>Validation, choose the "List" option then type: =INDIRECT(SUBSTITUTE($A$1," ","_"))

in the Source box. Ensure "In-cell dropdown" is checked and click Ok.

 

[JayReddy]

Hi Howard,

Thank you for the reply. I have tried this but I'm seeing an error stating "The Source currently evaluates to an error. Do you want to continue?". When I click 'Yes' it appears as though the drop-down option has been added to cell B1 although there are no values available for selection.

Any other ideas please? ...

Regards, Jay

 

[L. Howard]

Hi Jay,

I vaguely remembering the off-sheet use of a named range with INDIRECT is not (maybe is not...??) supported in Excel. That would be with dependent drop downs, while a named range can be used for the single drop down on another sheet.

Perhaps a way around that is to put your ranges on the same sheet in an obscure area of the sheet. Then its pretty much business as usual with dependent drop downs.

You can make the lists down-sheet and to the right far enough to not detract from the normal sheet workings. Then go a few more rows and a column or two below the lists and enter an "X".

From anywhere in the "working" range of the sheet you can do a Ctrl + End keystroke and it will take you to the lists.

You probably know you can make your drop down lists dynamic by using a formula like this in the refers to window.

=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)

Where you would change the column A to match your column and the $A$200 to a comfortable number of rows you would likely never exceed.

Howard

 

[L. Howard]

Hi again Jay,

Just to add for you dependent drop down.

You can copy this to the sheet module with the drop downs and it will do the folloing.

First drop down is in C2 and the dependent drop down is in C3,

When you select a item from the C2 drop down, it clears the current entry in the dependent drop down in C3 and selects that C3 drop down therefore making it active and awaiting a click on the down arrow.

Prevents you from inadvertently selecting a choice in C2 and leaving the old selection in C3, along with bypassing the need for you to manually select C3 for the secondary choice.

Of course, change the cells to match you cell drop downs address'.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub
  Range("C3").ClearContents
  Range("C3").Select
End Sub