INDIRECT SUBSTITUTE Referin to a dynamic list

(Another day of trial and error slips by...................)

I have a worksheet (Teams) with a list of Clubs across the first row and in the columns under each club I have a list of the teams belonging to that club. The Club list across row 1 is a Dynamic Name Range so new club names can be added without having to fiddle with the Name Range. (Dynamic_Clubs)

The list of teams under each club name is a normal Named list (=Teams!$A$2:$A$6) with the Club name (in cell A1 in this case) as the name of the list.

On a separate worksheet in cell G6 I have a drop down box that you can select a Club Name in. This drop down is from Data Validation and refers to the Clubs List (=Dynamic_Clubs)

Under the Club select list in cell G9 I have another drop down that shows the list of team names relevant to the Club selected in G6. This has the following in "Refers to"

 =INDIRECT(SUBSTITUTE($G$6," ",""))

All the above works fine, but being as I am, I want to make each of the team names list a Dynamic list. I achieved this for the first Club with by putting the following in the Refers to of the Name Manager.

 =OFFSET(Teams!$A$2,0,0,MATCH("*",Teams!$A:$A,-1)-1,1)

Here's the problem; Having made that a dynamic team list, while the club drop down list at G6 still works, the Teams drop down at cell G9 no longer drops down so doesn't show any of the team names for the selected club.

The two selections at G6 and G9 still work for any of the team lists I haven't changed to dynamic lists, but not for any of the dynamic team lists.

Any ideas on how I can get the ability to select a team at G9 and still have the list in column A of the Teams sheet dynamic please??

Just to add a bit further information, I pasted the INDIRECT SUBSTITUTE formula

=INDIRECT(SUBSTITUTE($G$6," ",""))

into a spare cell (G39). When I select the Club at Teams A1 in the dropdown at G6, cell G39 comes up with #REF!. If I select any other club (without the dynamic team list under it) cell G39 comes up with #VALUE!