Dependent Drop-down List

JayReddy

New Member
Joined
Sep 9, 2014
Messages
9
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:

AB
1Vendor RequestorVendor Approver
2Country Level1st Level Approver
3Company Code Level2nd Level Approver
43rd 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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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

New Member
Joined
Sep 9, 2014
Messages
9
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

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,439
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top