Indirect Validation - using data in second worksheet

Hi Excellers!
I have a competition entry form for an association I belong to that I'd like to add two drop down menus to so that entrants can select their club name and associated team names for that club.
In a worksheet behind the form I have a list of clubs in column "B" ("A" has the ID number in it as the table is from Access - don't ask!!) and the names of teams associated with that club going across the sheet beside the club name in individual Name Ranges. (Up to 30 Team names in Dynamic lists =OFFSET(TeamsTest!$C$2,0,0,COUNTA(TeamsTest!$C$3+TeamsTest!$C$3:$AZ$3),1) )
On the form I have managed to make the first drop down so the club can select its name from a list by using Data Validation and referring to the Range Name (ClubLink - =OFFSET(TeamsTest!$B$2,0,0,COUNTA(TeamsTest!$B$2:$B$200),1)). I have tried using an INDIRECT command to create a second drop down which will display only the team names associated with the Club selected in the first drop down, but can't get it to do anything - no drop down appears when you click on the drop down arrow at the side of the cell
The data Validation for the Team Name drop down is =INDIRECT(SUBSTITUTE($C$12," ","")) where C12 is the Club Name cell.
Any ideas why I can't get the Team Name cell to show the names listed beside the Club selected in C12?
Thanks

Frankie

Thanks for the responses but I think I may have to abandon the idea! After a bit more digging and experimenting it appears that not only can't you use Data Validation with a Dynamic Name list, but lists can only be in columns.
The original idea was to try and make up-dating the Club / Team list easier (i.e. won't have to reset the length of the Name list) for people with even less knowledge of Excel than me (!!) but it will just get toooooo complicated!
Thanks again

Hi Martin
My issue is with selecting a row across using an Indirect link through the Club selected in another DV cell. I've tried to re-create what I've currently got in the attached workbook

EXCELLENT!!! Thanks Mr. Dibbins and Mr. Wilson!!

Don't suppose you can make the DV work with a Dynamic list now can you??

Yes. That works for the Clubs column thanks. What of the Teams list in the rows across the columns? I tried to modify Club6 "refers to" data with =OFFSET(Sheet4!$D$9,0,0,COUNTA(Sheet4!$9:$9),1) thinking it would get down to the row and then look all the way across, but with this in place the third DV at B10 (which relates to the club selected in B6) won't drop down.
PS I added another three Clubs to the list to test the dynamic and then added another 3 teams to Club6

The spread sheet data I have is a collection of some 40 clubs that each have a selection of Teams against their name. A club may have a couple of team names against them or in one case, one club has 28 team names against it. I did have the Club names across the first row with the team names down the column below the club name, but the organiser who looks after the web site and is keeper of the Master copy of the data, wanted it in clubs down/ teams across format. If we can make the dynamic listing work in the Clubs across / Teams down format, I'm willing to try and get it changed, but I thin it's something to do with the original data coming out of an Access Database where it appears it has to be Club down / Team across.
The Club/Teams data on the spread sheet is used behind various forms we use where we don't want people entering variations of spelling or hieroglyphics Club & Team names!

The Yellow was the one Martin Wilson kindly did as an example, but it only went the first step. I then put in the Green one that is the Club Selection (Club1 / 2 etc) with the Orange one as the Team selector, driven by the selection in the Green one. They do both work without a problem thanks to the inputs from you both; However...... I wanted to make the Name lists on Sheet 4 Dynamic, so that every time you go in to add a Club or Team you don't have to change the parameters in the Name Manager. I had left a couple of blank cells at the end of each team list, but these then show up on the drop down and, where there is a long list of teams, was causing confusion with some of the users as the team names didn't actually appear unless you scrolled up the drop-down list (!! - Not very savvy some of our users!!). Going to a dynamic Name list seemed the way to go as the Clubs quite often enter different teams into competitions, but I read somewhere that you can't use the DV INDIRECT function where there is a dynamic Names list.

Hmmm.... Lost me here. The orange drop down doesn't do anything and there appears to be no DV in the cell. If I take the apostrophe away from the formula in cell D10 on sheet 1 it appears to just give a result from the first column after the Club selected in the green drop down.
Also, I can't see where the Name Range "Teams" is in Name Manager
I had to re-name the file as it wouldn't open with the exceedingly long name it came off the site with.

Second Test from ExcelForums.xlsx

Thanks for that. I'll give it a bit of a study to try and understand it then put it into my form. At least it works anyway!
Frankie