Help with Data Validation using INDIRECT function

I've attached part of a workbook that shows a table (headed in black) with dropdown options using data validation. The named ranges for all the dropdowns are on the right (Blue headed). Column A gives the option to select 'Banking' or 'Bookmaker'. If Banking is selected in column A, then column B, using the INDIRECT function as the source for the data validation, limits the dropdown selections to the named range 'Banking' in Column I. Alternatively, if 'Bookmaker' is selected then selections are limited to column J's named range which is 'Bookmaker'.

My problem is when using dropdowns with a third dependant list in column C, when there are many possible selections that could be made in column B. If I selected 'Bookmaker' in column A, I could choose from 28 bookmaker names in column B. With my current formula:

=INDIRECT(SUBSTITUTE(A3&B3," ",""))

...this would mean having to create 28 dropdown lists for when 'Bookmaker' is selected in column A and one of the 28 bookmakers is selected in column B. So far I have just created 2 dropdown lists for column C for when 'Bookmaker' is selected in column A and a bookmaker is selected in column B - they are in column W and X. In my actual workbook I have a list of over 400 bookmakers and so it would be impractical to create 400 named ranges for the dropdown lists.

The dropdown options in column C for when a bookmaker is selected in column B should always be:

Transfer
Charge
Correction
Start Bal.
Win
Lose
Confiscation
Bet

Is there a way of having the formula provide the above dropdown list in Column C, when 'Bookmaker' is selected in Column A and any bookmaker name from Column J is selected in Column B, without having to create 400 named ranges?

I was thinking along the lines of having the indirect formula being able to reference a lookup table. Thanks.

Dan

I don't see how without creating 400 named ranges. Column B was straight forward because only 2 options can be selected in column A, either 'Banking' or 'Bookmaker'. Both of these options have a named range in I and J respectively. For column C it seems a bit more complicated as I may have selected 'Bookmaker' in column A, 'Betfair' in column B. A named range would be needed for when 'Bookmaker' & 'Betfair' are selected. 27 other named ranges would be needed for the occasions when I had selected one of the other 27 bookmakers. As I say, my actual workbook has a list of over 400 bookmakers.

Is this something that can't be done without using VB script?

Hello
If the drop down options in Column C are always the same, as you state, for any Bookmaker, then you only need 1 Named range for the Bookmaker options and the same would apply to Banking. Link the Type to the Account Area drop down.

DBY

Hello
Take a look at the attachment. Unless I'm missing something, does this do what your're looking for?

DBY

Thanks DBY, that's exactly what I am after. It's been giving me a bit of a headache. I really appreciate your help. Thanks.

Dan