Dependant drop down list

Russk68

Well-known Member
Joined
May 1, 2006
Messages
589
Office Version
  1. 365
Platform
  1. MacOS
Hi All

I'm wondering if this is possible and if so I would greatly appreciate help with it.

I'm working between 2 sheets and I will use a Hotel as an example.

Sheet 1 has 2 columns, Floor# and Name.
Sheet 2 has 21 columns. The first column is the Floor#, the next 20 columns are names of people on that floor. Let's say there's 50 floors (Rows).

My goal is, to select a Floor# (A1) on Sheet 1 (already in a dropdown list) and then select (B1) a name from a dropdown list but the list only contains the names of people on that floor.
Hop
Can a formula or VB do this?

Thank you!

Russ
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Select the data on sheet 2 & then on the formula tab select "Create from Selection" in the Defined names section, Check "Left column" only, OK.
Then you can use a formula like
=Indirect(A1)
for the DV in B1.
If the "Floor#" is not a valid name for a named range, you will need to supply some examples.
 
Upvote 0
Hi Fluff
I couldn't get it to work.
AJ1:BJ1 are the header names: Mode1, Mode2 to Mode20
I selected AJ1:BC10 and "Create From Selection" and selected "Left Column Only". The named range is Mode1. When I select the Mode1 from the address bar, range AJ2:BC1 is selected. Is this right so far?
 
Upvote 0
What Fluff is having you do is:

If your Sheet2 has the A column identified as "Floor1", "Floor2", etc. and columns B:V contain the respective names, select all of that data.
Then, from the Formula tab, select Create From Selection. Be sure to indicate you want the name ranges to be from the LEFT COLUMN only.
Then, back on Sheet1, your drop down in B1 would use a LIST and in the Data Validation Source, enter: =INDIRECT(A1).

HTH.

it should look something like this:

Clip0001.jpg


Clip0002.jpg
 
Last edited:
Upvote 0
Hi
I finally realised the issue. This is the name format that I am using. MagicBlade-R (44ch)
I see that Named Ranges don't allow -,space or ()

I also see that a Named Range is created for every line. I plan on using a 1000 or so lines and if a name is changed, it does not update the Name.

Although I could make this work, is there a way of doing this other than using Named Ranges?

Thank you for all the help so far!
 
Upvote 0
Here's one way to do it:
It still uses a name range, but you only need 1 name range i.e "xFloor". So it doesn't matter if your data has -,space or () in it.
The "xFloor" will be created (& recreated) by macro.
Put the code in the sheet1 code window.

Code:
[FONT=lucida console][i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1103155-dependant-drop-down-list.html[/color][/i]
[color=Royalblue]Private[/color] [color=Royalblue]Sub[/color] Worksheet_SelectionChange([color=Royalblue]ByVal[/color] Target [color=Royalblue]As[/color] Range)
    [i][color=seagreen]'Range("B2:B200") is the range where data validation in col B exist, change it to suit[/color][/i]
    [color=Royalblue]If[/color] [color=Royalblue]Not[/color] Intersect(Target, Range([color=brown]"B2:B200"[/color])) [color=Royalblue]Is[/color] [color=Royalblue]Nothing[/color] [color=Royalblue]Then[/color]
        [color=Royalblue]If[/color] Target.Cells.Count = [color=crimson]1[/color] [color=Royalblue]Then[/color]
        [color=Royalblue]Dim[/color] c [color=Royalblue]As[/color] Range, tx [color=Royalblue]As[/color] [color=Royalblue]String[/color], res
            [color=Royalblue]With[/color] Sheets([color=brown]"Sheet2"[/color])
           
            res = Application.Match(Target.Offset(, -[color=crimson]1[/color]), .Range([color=brown]"A:A"[/color]), [color=Royalblue]False[/color])
            tx = [color=brown]"xFloor"[/color] [i][color=seagreen]'the name range[/color][/i]
                [color=Royalblue]If[/color] IsNumeric(res) [color=Royalblue]Then[/color]
                    [color=Royalblue]Set[/color] c = .Range([color=brown]"B"[/color] & res, .Cells(res, .Columns.Count).[color=Royalblue]End[/color](xlToLeft))
                    ActiveWorkbook.Names.Add Name:=tx, RefersTo:=c
                [color=Royalblue]Else[/color]
                    [color=Royalblue]On[/color] [color=Royalblue]Error[/color] [color=Royalblue]Resume[/color] [color=Royalblue]Next[/color]
                    ActiveWorkbook.Names(tx).Delete
                    [color=Royalblue]On[/color] [color=Royalblue]Error[/color] [color=Royalblue]GoTo[/color] [color=crimson]0[/color]
                    MsgBox [color=brown]"Can't find the floor"[/color]
                [color=Royalblue]End[/color] [color=Royalblue]If[/color]
           
            [color=Royalblue]End[/color] [color=Royalblue]With[/color]
        [color=Royalblue]End[/color] [color=Royalblue]If[/color]
    [color=Royalblue]End[/color] [color=Royalblue]If[/color]
[color=Royalblue]End[/color] [color=Royalblue]Sub[/color]
 
[/FONT]
 
Last edited:
Upvote 0
Sorry, I forgot one thing:
The formula for the data validation should be: =xFloor
 
Upvote 0
Hi
I was using the sheets and columns in my original post as an example to keep it simple.

My sheet names and ranges are different as I was expecting to be able to adjust to fit my need.

I changed "Sheet2" to the actual sheet name and I almost have this working. The range that the data is in, in Sheet 2 is AJ:BC. The drop down list is currently showing results from A:BC. So it is working but I have to scroll way down to see the correct data.

I tried adjusting your code so the list only shows data from AJ:BC but haven't succeeded.

Thank you for your help!
 
Upvote 0
So "the floor" (or whatever it is), is it in col A or AJ?
If it's in col AJ & "the name" start in col AK then:

Try changing this:
res = Application.Match(Target.Offset(, -1), .Range("A:A"), False)
to this:
res = Application.Match(Target.Offset(, -1), .Range("AJ:AJ"), False)

and this:
Set c = .Range("B" & res, .Cells(res, .Columns.Count).End(xlToLeft))
to this:
Set c = .Range("AK" & res, .Cells(res, .Columns.Count).End(xlToLeft))
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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
Back
Top