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

 

[Russk68]

Hi Akuini

That fixed it!
Would you be so kind to help me with 1 more fix?

In Sheet1, column B is the floor# and column C is the persons name. Column D is other information. I would like to clear the row in column C & D when the floor# is removed.

This would be great.

Thank you!!!

 

[Akuini]

Hi Akuini

In Sheet1, column B is the floor# and column C is the persons name. Column D is other information. I would like to clear the row in column C & D when the floor# is removed.

OK, add this code to the sheet1 code window:

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)
[I][COLOR=seagreen]'change both Range("B2:B10") below to suit[/COLOR][/I]

[COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"B2:B10"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
    [COLOR=Royalblue]Dim[/COLOR] c [COLOR=Royalblue]As[/COLOR] Range
    [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] c [COLOR=Royalblue]In[/COLOR] Intersect(Target, Range([COLOR=brown]"B2:B10"[/COLOR]))
        [I][COLOR=seagreen]'clear the row in column C & D when the floor# (col B) is removed.[/COLOR][/I]
        [COLOR=Royalblue]If[/COLOR] Len(c.Value) = [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR] c.Offset(, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]""[/COLOR]: c.Offset(, [COLOR=crimson]2[/COLOR]) = [COLOR=brown]""[/COLOR]
    [COLOR=Royalblue]Next[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

Note:
If you have ~1000 item of floor then I suggest for the data validation of floor (sheet1 col B) you use a searchable combobox instead of data validation, because selecting from ~1000 items is difficult.
You can find an example of a searchable combobox in this thread:
https://www.mrexcel.com/forum/excel-questions/1089693-data-entry-force-selection.html

 

[Russk68]

That works great!

This has greatly improved my productivity.

Also, commenting the link to this post is brilliant! I will be doing that for now on.

Thank you very much for your time. It is greatly appreciated!!!

Russ

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.