Data Validation Formula needed to fill sequence by dragging the selection.

Hi there,

If the user selects a specific Product category in column F through a dropdown menu from list '.'!$B$2:$B$9. (data validation formula: ='.'!$B$2:$B$10).

Then Attribute name 1-20 will be prefilled with information of tab '.'. The attributes are linked to a specific product category. This overview can be found on tab '.'. The following formula is used:
=ALS('3. Product Attributes'!$F9='.'!$B$2;'.'!$B$15;ALS($F9='.'!$B$3;'.'!$B$45;ALS($F9='.'!$B$4;'.'!$B$75;ALS($F9='.'!$B$5;'.'!$B$105;ALS($F9='.'!$B$6;'.'!$B$135;ALS($F9='.'!$B$7;'.'!$B$165;ALS($F9='.'!$B$8;'.'!$B$195;ALS($F9='.'!$B$9;'.'!$B$225;0))))))))

Then for each Attribute value a specific dropdown menu should be available to the user. This overview can be found on tab '.'. I have made the following data validation formula: =ALS(F9='.'!B2;'.'!B16:B44; ALS(F9='.'!B3;'.'!B46:B74; ALS(F9='.'!B4;'.'!B76:B104; ALS(F9='.'!B5;'.'!B106:B134; ALS(F9='.'!B6;'.'!B136:B164; ALS(F9='.'!B7;'.'!B166:B194; ALS(F9='.'!B8;'.'!B196:B224; ALS(F9='.'!B9;'.'!B226:B254;0))))))))

This formula works. However, I am not able to fill the sequence by dragging the selection. This needs to be done for 4000 cells. Therefore, I tried the following data validation formula:
=ALS(F9='.'!$B$2;'.'!B$16:$B$44; ALS(F9='.'!$B$3;'.'!B$46:B$74; ALS(F9='.'!$B$4;'.'!B$76:B$104; ALS(F9='.'!$B$5;'.'!B$106:B$134; ALS(F9='.'!$B$6;'.'!B$136:B$164; ALS(F9='.'!$B$7;'.'!B$166:B$194; ALS(F9='.'!$B$8;'.'!B$196:B$224; ALS(F9='.'!$B$9;'.'!B$226:B$254;0))))))))

Unfortunately, this formula is too long, since it exceeds the limitation of characters. I am also not able to fill the sequence by dragging the selection.

How can I solve this?

Thanks in advance!