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?