I have several columns with time values. With a Data Validation, I display a list that takes the values from a hidden column with a large range of time values. Depending on the value of another field, I can take portions of the range to display the value list. That works perfectly:
Source:
=IF($b$6=$X$8;$X$13:$X$171;$X$193:$X$521)
SCREENSHOT: for a certain value in B6, it takes a certain portion of range X
Now, in the rows of column C I want a time list with values starting from 10 minutes later than column B.
For this, I can perfectly trace the position of B+10 minutes with the Match function. I also add +12 because the list starts at row 13:
=MATCH(B13+TIME(0;10;0);X13:X326;1)+12
=> results in 24
As from there, I can perfectly define the range I want:
="$X"& MATCH(B13+TIME(0;10;0);X13:X326;1)+12 & ":$X100"
=> results in $X24:$X100
When I paste this formula into the Source field, I get an alert "The list source must be a delimited list, or a reference to single row or column."
SCREENSHOT: Alert on range formula
As a workaround, I placed the formula in a cell, but then it lists the string as literal text, not as a range (see screenshot).
How do I learn the Source field that this is a range?
SCREENSHOT: it applies the string as literal text, not as range
This is a recurring question.
Just put your formula (that generates the range as a string) in a cell and in the drop-down list source field use Excel's INDIRECT command.
If your formula
="$X"& MATCH(B13+TIME(0;10;0);X13:X326;1)+12 & ":$X100"
is in cell Z15, put into data validation list source:
=INDIRECT(Z15)