How to propagate data validation to other rows?

Question

Sheet Ranges:

Sheet Work:

In sheet Ranges in cells A1:A5 I have some values, let's say: One, Two, Three, Four, Five. In cell B1 I have the following formula: =ARRAYFORMULA(transpose(if(Work!$A1<>"";$A$1:$A$5;""))) which fills cells B1:F1 with above mentioned values based on whether the cell A1 in sheet Work is non-empty.

Next, I've selected cells B1:F1 and then using fill-handle dragged the selection down to cover range B1:F10. This copied the above range but each row now depends on the corresponding row in sheet Work.

Basically, I've created a list of ranges of data that are only filled when certains cells in another sheet are non-empty.

Finally, in sheet Work I've added data validation for cell B1 with a dropdown list: Ranges!B1:F1

What this does is creates a dropdown list in this cell with values: One, Two, Three, Four, Five but only if cell A1 is non-empty!

Now for my question - I would like to propagate this data validation to other rows (1 - 10) with fill-handle but so that the dropdown list depends on a different cell/row in each case. But the fill-handle just copies the validation rule from the first row in each case. (You can see in the 2nd picture that even for rows with empty cell in column A there are still dropdowns with values.

How can I propagate this without having to change the cell ranges in data validation rules manually in each case?

Answer 1

Since no one proposed anything better I'm closing it with my solution from the comment. I've used a script that sets the data validation for each cell separately. I'm not posting code since my actual sheet is quite different from this example and I would have to rewrite the whole thing.

This solution is far from ideal, mostly because you have to spend time googling the syntax and functions and also not everyone will even want to have scripts in their sheet. But apparently there's nothing better.

It's worth mentioning that fill-handle does what I want in Excel. I've tried creating this sheet in excel and opening it in google sheets but was disappointed to find out that the data validation rules were lost/broken in the process.