Dependent Drop down list upon another Drop down list

X

XCaliber

New Member
Joined
Jan 16, 2014
Messages
41
I have 3 Different worksheets within the same workbook.

Worksheet 1 contains this Table 1 Table Header is "System"(Not full table):

All Systems are entered by hand.

169 G. Canis Majoris
19 Leonis Minoris
44 b Ophiuchi
69 G. CARINAE
85 Pegasi
Abaana
Aeduwona
Aisoci
Albarib
Altair

<colgroup><col style="width: 191px"></colgroup><tbody>
[TD="bgcolor: #000000"] System
[/TD]

[TD="bgcolor: #dce6f1"] 16 C URSAE MAJORIS
[/TD]

[TD="bgcolor: #dce6f1"] 18 PUPPIS
[/TD]

[TD="bgcolor: #dce6f1"] 39 Tauri
[/TD]

[TD="bgcolor: #dce6f1"] 54 G. ANTLIA
[/TD]

[TD="bgcolor: #dce6f1"] 78 Ursae Majoris
[/TD]

[TD="bgcolor: #dce6f1"] AASGAA
[/TD]

[TD="bgcolor: #dce6f1"] AC Yax Baru
[/TD]

[TD="bgcolor: #dce6f1"] AGASTANI
[/TD]

[TD="bgcolor: #dce6f1"] Alano
[/TD]

[TD="bgcolor: #dce6f1"] Alioth
[/TD]

</tbody>







Worksheet 2 contains This Table 2 Headers are "System" and "Station" (not full table):

The System Column is using the Data Validation List with the following formula to Display All Systems from Table 1: =INDIRECT("TBL_System_Information[System]") which refers to Table 1 worksheet 1. Station Column is entered by hand.


39 Tauri
Porta
78 Ursae Majoris
Townshend Hub
85 Pegasi
Buckland Gateway
Alano
Bondar City
Alano
Walker Enterprise
Albarib
Cheli Terminal
Albarib
Hadfield Station
Albarib
Lorentz Orbital
Albarib
Newcomen Port
Altair
Grandin Gateway
Altair
Tereshkova Dock

<colgroup><col style="width: 191px"><col width="205"></colgroup><tbody>
[TD="bgcolor: #000000"] System
[/TD]
[TD="bgcolor: #000000"] Station
[/TD]

[TD="bgcolor: #dce6f1"] 16 C URSAE MAJORIS
[/TD]
[TD="bgcolor: #dce6f1"] Daimler Station
[/TD]

[TD="bgcolor: #dce6f1"] 44 b Ophiuchi
[/TD]
[TD="bgcolor: #dce6f1"] Feustel Gateway
[/TD]

[TD="bgcolor: #dce6f1"] 85 Pegasi
[/TD]
[TD="bgcolor: #dce6f1"] Borman Terminal
[/TD]

[TD="bgcolor: #dce6f1"] Abaana
[/TD]
[TD="bgcolor: #dce6f1"] Edwards Installation
[/TD]

[TD="bgcolor: #dce6f1"] Alano
[/TD]
[TD="bgcolor: #dce6f1"] Leestma Port
[/TD]

[TD="bgcolor: #dce6f1"] Albarib
[/TD]
[TD="bgcolor: #dce6f1"] Aubakirov Hub
[/TD]

[TD="bgcolor: #dce6f1"] Albarib
[/TD]
[TD="bgcolor: #dce6f1"] Edison Hub
[/TD]

[TD="bgcolor: #dce6f1"] Albarib
[/TD]
[TD="bgcolor: #dce6f1"] Hale Orbital
[/TD]

[TD="bgcolor: #dce6f1"] Albarib
[/TD]
[TD="bgcolor: #dce6f1"] Mayr Station
[/TD]

[TD="bgcolor: #dce6f1"] Albarib
[/TD]
[TD="bgcolor: #dce6f1"] Northrop Terminal
[/TD]

[TD="bgcolor: #dce6f1"] Altair
[/TD]
[TD="bgcolor: #dce6f1"] Solo Orbiter
[/TD]

</tbody>






On Worksheet 3 has the following table 3:

In the System Column I am using the following formula within Data Validation Source box to Display All Systems from Table 1: =INDIRECT("TBL_System_Information[System]")


[TABLE="width: 984"]
<colgroup><col><col width="*"></colgroup><tbody>[TR]
[TD="bgcolor: #4f81bd"]
System​
[/TD]
[TD="bgcolor: #4f81bd"]
Station​
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD="bgcolor: #f3f3f3"]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD="bgcolor: #f3f3f3"]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD="bgcolor: #f3f3f3"]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD="bgcolor: #f3f3f3"]
[/TD]
[/TR]
</tbody>[/TABLE]




I want the Station column to be a dependent drop down list based on the System Column drop down list to only show the stations within the System selected in the System Column (If System has more than one station). Example: System: Albarib has more than 1 station.

I have no idea what formula to use to make this happen.

Any help would be greatly appreciated.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
XCaliber said:
I have 3 Different worksheets within the same workbook.

Worksheet 1 contains this Table 1 Table Header is "System"(Not full table):

All Systems are entered by hand.

169 G. Canis Majoris
19 Leonis Minoris
44 b Ophiuchi
69 G. CARINAE
85 Pegasi
Abaana
Aeduwona
Aisoci
Albarib
Altair

<colgroup><col style="width: 191px"></colgroup><tbody>
[TD="bgcolor: #000000"] System
[/TD]

[TD="bgcolor: #dce6f1"] 16 C URSAE MAJORIS
[/TD]

[TD="bgcolor: #dce6f1"] 18 PUPPIS
[/TD]

[TD="bgcolor: #dce6f1"] 39 Tauri
[/TD]

[TD="bgcolor: #dce6f1"] 54 G. ANTLIA
[/TD]

[TD="bgcolor: #dce6f1"] 78 Ursae Majoris
[/TD]

[TD="bgcolor: #dce6f1"] AASGAA
[/TD]

[TD="bgcolor: #dce6f1"] AC Yax Baru
[/TD]

[TD="bgcolor: #dce6f1"] AGASTANI
[/TD]

[TD="bgcolor: #dce6f1"] Alano
[/TD]

[TD="bgcolor: #dce6f1"] Alioth
[/TD]

</tbody>







Worksheet 2 contains This Table 2 Headers are "System" and "Station" (not full table):

The System Column is using the Data Validation List with the following formula to Display All Systems from Table 1: =INDIRECT("TBL_System_Information[System]") which refers to Table 1 worksheet 1. Station Column is entered by hand.


39 Tauri
Porta
78 Ursae Majoris
Townshend Hub
85 Pegasi
Buckland Gateway
Alano
Bondar City
Alano
Walker Enterprise
Albarib
Cheli Terminal
Albarib
Hadfield Station
Albarib
Lorentz Orbital
Albarib
Newcomen Port
Altair
Grandin Gateway
Altair
Tereshkova Dock

<colgroup><col style="width: 191px"><col width="205"></colgroup><tbody>
[TD="bgcolor: #000000"] System
[/TD]
[TD="bgcolor: #000000"] Station
[/TD]

[TD="bgcolor: #dce6f1"] 16 C URSAE MAJORIS
[/TD]
[TD="bgcolor: #dce6f1"] Daimler Station
[/TD]

[TD="bgcolor: #dce6f1"] 44 b Ophiuchi
[/TD]
[TD="bgcolor: #dce6f1"] Feustel Gateway
[/TD]

[TD="bgcolor: #dce6f1"] 85 Pegasi
[/TD]
[TD="bgcolor: #dce6f1"] Borman Terminal
[/TD]

[TD="bgcolor: #dce6f1"] Abaana
[/TD]
[TD="bgcolor: #dce6f1"] Edwards Installation
[/TD]

[TD="bgcolor: #dce6f1"] Alano
[/TD]
[TD="bgcolor: #dce6f1"] Leestma Port
[/TD]

[TD="bgcolor: #dce6f1"] Albarib
[/TD]
[TD="bgcolor: #dce6f1"] Aubakirov Hub
[/TD]

[TD="bgcolor: #dce6f1"] Albarib
[/TD]
[TD="bgcolor: #dce6f1"] Edison Hub
[/TD]

[TD="bgcolor: #dce6f1"] Albarib
[/TD]
[TD="bgcolor: #dce6f1"] Hale Orbital
[/TD]

[TD="bgcolor: #dce6f1"] Albarib
[/TD]
[TD="bgcolor: #dce6f1"] Mayr Station
[/TD]

[TD="bgcolor: #dce6f1"] Albarib
[/TD]
[TD="bgcolor: #dce6f1"] Northrop Terminal
[/TD]

[TD="bgcolor: #dce6f1"] Altair
[/TD]
[TD="bgcolor: #dce6f1"] Solo Orbiter
[/TD]

</tbody>






On Worksheet 3 has the following table 3:

In the System Column I am using the following formula within Data Validation Source box to Display All Systems from Table 1: =INDIRECT("TBL_System_Information[System]")


[TABLE="width: 984"]
<colgroup><col><col width="*"></colgroup><tbody>[TR]
[TD="bgcolor: #4f81bd"]
System​
[/TD]
[TD="bgcolor: #4f81bd"]
Station​
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD="bgcolor: #f3f3f3"]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD="bgcolor: #f3f3f3"]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD="bgcolor: #f3f3f3"]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD="bgcolor: #f3f3f3"]
[/TD]
[/TR]
</tbody>[/TABLE]




I want the Station column to be a dependent drop down list based on the System Column drop down list to only show the stations within the System selected in the System Column (If System has more than one station). Example: System: Albarib has more than 1 station.

I have no idea what formula to use to make this happen.

Any help would be greatly appreciated.
Click to expand...

XCaliber,
I don't know if you are still looking for a solution…if you are…here is one way.
Make the following formula the 'Source' in whatever cell you want to have the drop down 'List':
Code: 
=OFFSET($H$2,0,0,COUNTA($H:$H)-1)
Column F is a helper column. Put this formula in cell F2:
Code: 
=RIGHT($D2,7)

Put the 2 macros below in a Worksheet module, select the sheet tab, then 'View Code', then paste in the largest window.
Then SaveAS a macro-enabled worksheet.
This is what the sheet looked like after I ran the macro:

Excel 2007
CDEFGHI
1SystemStation=RIGHT($D2,7)
216 C URSAE MAJORISDaimler StationStationAlbaribHadfield Station
339 TauriPortaMayr Station
444 b OphiuchiFeustel Gateway
578 Ursae MajorisTownshend Hub
685 PegasiBorman Terminal
785 PegasiBuckland Gateway
8AbaanaEdwards Installation
9AlanoBondar City
10AlanoLeestma Port
11AlanoWalker Enterprise
12AlbaribAubakirov Hub
13AlbaribCheli Terminal
14AlbaribEdison Hub
15AlbaribHadfield Station
16AlbaribHale Orbital
17AlbaribLorentz Orbital
18AlbaribMayr Station
19AlbaribNewcomen Port
20AlbaribNorthrop Terminal
21AltairGrandin Gateway
22AltairSolo Orbiter
23AltairTereshkova Dock
24CDEFGH
Sheet1


To Run these macros:
Open the file and 'enable macros', then
Enter any column C value in cell G2, then any value in column D with the text 'Station' at the end will be placed in column H, beginning with H2.
With this solution you may not need the drop down List, but since that is where you were trying to go I, included the OFFSET formula above.
Perpa

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$2" Then StationFind
End Sub

Sub StationFind()
Dim rw As Long
Dim n As Long
Dim LastHrow As Long

    Application.ScreenUpdating = False
    Range("H2:H24").ClearContents

    LastHrow = 2

    For rw = 2 To 24
        If rw <> 2 Then
             Range("F2").Copy     'This is the formula to find PicName
             Range("F" & rw).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        End If

        If Cells(rw, 3) = Cells(2, 7) And Cells(rw, 6) = "Station" Then
            Cells(LastHrow, 8) = Cells(rw, 4)
        End If

        LastHrow = Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Row

    Next rw
    Range("F3:F24").ClearContents
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Custom Data Validation drop downs dependent on a cell value
Replies
5
Views
756
myall_blues
myall_blues
P
  • Question Question
Dependent drop down list
Replies
0
Views
405
PurpleCat88
P
M
  • Solved
Result of Filtered Array (Excel 365) to appear as drop down list in data validation
Replies
3
Views
617
kevin9999
K
M
  • Question Question
Data Validation Dependent Drop Down Box Error
Replies
4
Views
639
Fluff
Fluff
M
  • Solved
Data Validation Drop-Down List Reference in Another Sheet
Replies
8
Views
627
miffy45
M

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

Share this page

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