Dependent Drop down list upon another Drop down list

[XCaliber]

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.

System

16 C URSAE MAJORIS

169 G. Canis Majoris

18 PUPPIS

19 Leonis Minoris

39 Tauri

44 b Ophiuchi

54 G. ANTLIA

69 G. CARINAE

78 Ursae Majoris

85 Pegasi

AASGAA

Abaana

AC Yax Baru

Aeduwona

AGASTANI

Aisoci

Alano

Albarib

Alioth

Altair

<colgroup><col style="width: 191px"></colgroup><tbody>
</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.

System	Station
16 C URSAE MAJORIS	Daimler Station
39 Tauri	Porta
44 b Ophiuchi	Feustel Gateway
78 Ursae Majoris	Townshend Hub
85 Pegasi	Borman Terminal
85 Pegasi	Buckland Gateway
Abaana	Edwards Installation
Alano	Bondar City
Alano	Leestma Port
Alano	Walker Enterprise
Albarib	Aubakirov Hub
Albarib	Cheli Terminal
Albarib	Edison Hub
Albarib	Hadfield Station
Albarib	Hale Orbital
Albarib	Lorentz Orbital
Albarib	Mayr Station
Albarib	Newcomen Port
Albarib	Northrop Terminal
Altair	Grandin Gateway
Altair	Solo Orbiter
Altair	Tereshkova Dock

<colgroup><col style="width: 191px"><col width="205"></colgroup><tbody>
</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]")

System​	Station​

<colgroup><col><col width="*"></colgroup><tbody>
</tbody>

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.

 

[Perpa]

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.

System

16 C URSAE MAJORIS

169 G. Canis Majoris

18 PUPPIS

19 Leonis Minoris

39 Tauri

44 b Ophiuchi

54 G. ANTLIA

69 G. CARINAE

78 Ursae Majoris

85 Pegasi

AASGAA

Abaana

AC Yax Baru

Aeduwona

AGASTANI

Aisoci

Alano

Albarib

Alioth

Altair

<colgroup><col style="width: 191px"></colgroup><tbody>
</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.

System	Station
16 C URSAE MAJORIS	Daimler Station
39 Tauri	Porta
44 b Ophiuchi	Feustel Gateway
78 Ursae Majoris	Townshend Hub
85 Pegasi	Borman Terminal
85 Pegasi	Buckland Gateway
Abaana	Edwards Installation
Alano	Bondar City
Alano	Leestma Port
Alano	Walker Enterprise
Albarib	Aubakirov Hub
Albarib	Cheli Terminal
Albarib	Edison Hub
Albarib	Hadfield Station
Albarib	Hale Orbital
Albarib	Lorentz Orbital
Albarib	Mayr Station
Albarib	Newcomen Port
Albarib	Northrop Terminal
Altair	Grandin Gateway
Altair	Solo Orbiter
Altair	Tereshkova Dock

<colgroup><col style="width: 191px"><col width="205"></colgroup><tbody>
</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]")

System​	Station​

<colgroup><col><col width="*"></colgroup><tbody>
</tbody>

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.

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':

=OFFSET($H$2,0,0,COUNTA($H:$H)-1)

Column F is a helper column. Put this formula in cell F2:

=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
	C	D	E	F	G	H	I
1	System	Station		=RIGHT($D2,7)
2	16 C URSAE MAJORIS	Daimler Station		Station	Albarib	Hadfield Station
3	39 Tauri	Porta				Mayr Station
4	44 b Ophiuchi	Feustel Gateway
5	78 Ursae Majoris	Townshend Hub
6	85 Pegasi	Borman Terminal
7	85 Pegasi	Buckland Gateway
8	Abaana	Edwards Installation
9	Alano	Bondar City
10	Alano	Leestma Port
11	Alano	Walker Enterprise
12	Albarib	Aubakirov Hub
13	Albarib	Cheli Terminal
14	Albarib	Edison Hub
15	Albarib	Hadfield Station
16	Albarib	Hale Orbital
17	Albarib	Lorentz Orbital
18	Albarib	Mayr Station
19	Albarib	Newcomen Port
20	Albarib	Northrop Terminal
21	Altair	Grandin Gateway
22	Altair	Solo Orbiter
23	Altair	Tereshkova Dock
24	C	D	E	F	G	H
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

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