Dependant drop down list

Russk68

Active Member
Joined
May 1, 2006
Messages
471
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,762
Office Version
365
Platform
Windows
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.
 

Russk68

Active Member
Joined
May 1, 2006
Messages
471
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?
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,074
Office Version
365, 2010
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:



 
Last edited:

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,074
Office Version
365, 2010
And...

 

Russk68

Active Member
Joined
May 1, 2006
Messages
471
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!
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
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:

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
Sorry, I forgot one thing:
The formula for the data validation should be: =xFloor
 

Russk68

Active Member
Joined
May 1, 2006
Messages
471
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!
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,102,508
Messages
5,487,302
Members
407,590
Latest member
Grobler

This Week's Hot Topics

Top