Dependant drop down list

Russk68

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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,479
Office Version
  1. 365
Platform
  1. 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
473
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,260
Office Version
  1. 365
  2. 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,260
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

And...

 

Russk68

Active Member
Joined
May 1, 2006
Messages
473
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,575
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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,575
Office Version
  1. 365
Platform
  1. Windows
Sorry, I forgot one thing:
The formula for the data validation should be: =xFloor
 

Russk68

Active Member
Joined
May 1, 2006
Messages
473
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,575
Office Version
  1. 365
Platform
  1. 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,109,275
Messages
5,527,726
Members
409,784
Latest member
AdamPriest

This Week's Hot Topics

Top