Excel multiple dynamic dropdown list without blanks

Question

I am struggling with following exercise:

Here is a table for the part of data. The first dependent drop-down should belong to annealing (lets say this is the first dropdown) and the second one should be dynamic for material.

I've heard it is possible with function:

Offset, Index, Match, Offset, Counta, but I am unable to finish and close this function in 250 characters as formula to use dropdown list. The second thing that there should be visible only what is below material for specific material, there should not be any blanks.

Here is the example how does it look like, but it has to be enriched to work properly. I presume - by one offset: =Offset(Idex(A:B;Match(D9;A:B;0));1;Countblank(Match(D9;A:B;0)))

What is more, there should not be indirect formula, because the range of names got used somewhere else, so it's highly recommended to avoid.

As excel formula, no macros please just to keep it simple.

Could you help me with it?

Please let me know if something is not clear.

Answer 1

Create one named range that includes the row where all your materials are listed, starting with "Mart" and going across in the row.

Then you need to create individual named ranges, one for each material. You can just select the cells under "Mart" and give them the range name "Mart". These range names cannot be dynamic, though, they need to point to fixed ranges if you want to use them in dependent data validation.

Edit: If you use relative references in the right places and keep your wits about you when you define them, then you can create one single range name formula for the dependent drop-down.

Consider the following screenshot:

G2 has data validation with a fixed range name for the materials. H2 has a range name that calculates depending on what is in cell G2. The formula is

=OFFSET(Sheet1!$A$3:$A$10,0,MATCH(Sheet1!G2,Sheet1!$B$2:$E$2,0),COUNTA(OFFSET(Sheet1!$A$3:$A$10,0,MATCH(Sheet1!G2,Sheet1!$B$2:$E$2,0))),1)

When you define this range name, the active cell must be in cell H2, because it will look to the relative reference G2 for the input to the Match function.

Although the different materials have a varying number of rows underneath, the range will adjust to that and only apply to the populated cells.