Dependant drop down list

[Russk68]

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

 

[Fluff]

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]

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]

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:

 

[kweaver]

And...

 

[Russk68]

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]

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.

[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]

 

[Akuini]

Sorry, I forgot one thing:
The formula for the data validation should be: =xFloor

 

[Russk68]

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]

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))