function indirect doesn't work with data validation

In your drop-down in A2 you have "AA", "BB", "CC" and "DD". None of these strings are named ranges. Therefore =INDIRECT(A2) just returns A2.

Do the following.

1. Set range BRAND1 = F2:F3, BRAND2 = G2:G3, BRAND3 = H"H3 and Type1 = I2:I3.
2. In the validation of A2 change the list range to =She!F1:i1

Now the drop-down in A2 will list the ranges "BRAND1", "BRAND2", "BRAND3" and "Type1". and the drop-down in B2 will list the items contained in the 4 ranges using the list  =INDIRECT(A2).

Makjil

Based on your discussion point below, I suggest you delete the data validation formula (for B2 in Sheet 1) and in that cell add the formula (with inverted commas within the INDIRECT part) as follows:

=INDEX(sh!$F$2:$I$3,2,MATCH(INDIRECT("A2"),BRAND,0))

You'll then see the type related to the brand in A2 will then appear in B2 (e.g. ASD-4 if A2="DD"). Note that the named range BRAND is already defined in "sh".

(The second argument of INDIRECT defines the referencing syle; luckily you're using A1 style referencing which is the default so you don't need to specify TRUE for the second argument).

Actually INDIRECT does nothing in that formula. This works the just the same:

=INDEX(sh!$F$2:$I$3,2,MATCH(A2,BRAND,0))

Revision: I hadn't spotted that the tables in sheet "sh" had unequal numbers of Brands. The attached file has a single table (with named ranges BRAND and TYPE) so the formula in B2 is simplied to: 

=INDEX(TYPE,MATCH(A2,BRAND,0))

As you add rows directly below the table, it expands and the selections in A2 include the new BRAND row (and B2 can show the new TYPE) since data validation in A2 now refers to BRAND.

EXTRA (works only in Excel 365): I've now added a second workbook (T with table and data validation v0_b.xlsm) where SHEET1 allows you to set the Brand in A3, the chose from the types in B3 (data validation limits those to the items matching that in the table in sheet "sh" under the heading Product data). The price from that table is also displayed (and if you put a quantity in D3, the total appears in E3. Change B3's choice to AA (from the  dropdown list) and you'll see the products in the dropdown of B3.

The data validation in B3 is however quite complex. The Custom formula fro data validation is simply:

=sh!$G$3#

That refers to sheet "sh" where, under the Data validation heading, cell G3 has the (array) formula:

=FILTERXML("<t><s>"&SUBSTITUTE(G2,",","</s><s>")&"</s></t>","//s")

where, in turn, cell G2 is the formula:

=TEXTJOIN(",",TRUE,IF(BRAND=SHEET1!A3,TYPE,""))

Note that in SHEET1, the data validation formula now refers to the (named) table under Master brand list in sh. This has it's own data validation (to prevent you adding the same brand a second time (i.e. they are unique values). Under Product data, ydata validation prevents you adding a line (or chnaging a line) to a brand which is not already in that Master brand list in sh- that way cell A3 will always have the brands list (with other details) under Product data.

Also I had to add a simple workbook event macro (below) to force the selection in B3 to be blanked when A3 changes (otherwise you'd get a price for a type not associated with the new Brand selection):

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A3")) Is Nothing Then Exit Sub
Range("B3").Value = ""

End Sub

Hope this is what you need.