INDIRECT SUBSTITUTE refering 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"

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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!

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi John

Demo workbook attached. Tabs "Normal Name" and "Normal Name Select" use the non-dynamic naming for the team lists under each club name (in row 1)
Tabs "Dynamic Name" and "Dynamic Name Select" are the same thing but the team lists are now dynamic named lists.

As you will see the "Select a Club" drop down works in both, but the "Select a Club Team" drop down only works in the "Normal" sheets, not when the team lists are dynamic.

Hoppe this all makes sense!

Thanks

Frankie

INDIRECT will not work with Dynamic Lists in the "standard" way

See "Using Dynamic Lists" in the attached which explains how to solve this problem ..


http://www.contextures.com/xlDataVal02.html#Dynamic

Still a bit lost here trying to get the team selection working on the dynamic team lists John!
The code

Please Login or Register  to view this content.

(D3 is where the Club Selection is) still produces #REF! when typed in a cell and no drop down when made the reference in Data validation.
The instruction on the linked site suggests the name range is in one column while the list is in another. In my case the name range (Clubs) is across the first row and each list is under the club name. Is this screwing it up??

Really stuck with this one as it pushes me out of my limited "comfort zone"!!!

See attached.

in sheet "Dynamic Range" I have re-arranged the Data:

Column A is "Dynamic_Club_Name"

in Columns B:E

Row 1 entrres are named "DClub_n" where n is 1,2,3 etc

COLUMNS B:E are named "DClub_n_Col" where n=1,2, 3 etc

In "Dynamic Range Select"

in D3

DV list refers to: "Dynamic_Club_Name"

in D6

DV Refers to:

=OFFSET(INDIRECT(SUBSTITUTE($D3," ","_")),0,0,COUNTA(INDIRECT(SUBSTITUTE($D3," ","_")&"_Col")),1)

SUBSTITUTE replace spaces with underscore.

IN "Sheet1" I reproduced the CONTEXTURE example

Thanks for the time to resolve this for me John. Unfortunately the club name and teams have to remain in the layout I had (Clubs across Row 1 / Teams below each club name) because of the way the information is integrated with a major database on the organisations website. (I am just a small cog in a big machine!!).

'Tis great to see how it could be achieved though as this will save me time on a later project I've got.

Thanks again.

Frankie