Data validation dependent on cell value

I have created two data validations in columns A and B (Sheet2) by taking the unique values from columns A and B in Sheet1.

The problem is that when you choose a value in column A, then you can choose ALL values from column B. I want the displayed
values in the validation list in B to contain only the matching combinations, not all the values. That way you can't add an invalid
combination of A and B. In sheet 1 all the possible, valid combinations are listed and in sheet 2 you add rows.

I found the following to be very popular:

Please Login or Register  to view this content.

It can create the list I want, by changing 1:1 to 2:2, 3:3 etc. but you use x cells (as many as the combinations). How can I
incorporate this in the validation list? That is, to put the above as a formula in the list.

Is there another way to look into?

My source data for the validation (in Sheet1) are in the form of rows. I have seen that method but it groups data
in columns. This means that I have to create 30-40-50 or more named lists. If I had 3-4 groups it would make sense.

If I can't find a solution I could do that through my macro and create automatically all the lists. But I suppose there
is an easier solution.

I am close to solving the problem but I have an issue with Named Ranges. How can I bypass the problem and use any name I want in the range?

I have found the solution, which is kind of complicated but it works well with dynamic lists.