Google Sheets – Dynamic Calculation of Data Validation

Question

My use case is to select between two times within a single day.

• The first cell ("from" time) has list of items data validation set up, where the options are as follows:

  12:00 am,1:00 am,2:00 am,3:00 am,4:00 am,5:00 am,6:00 am,7:00 am,8:00 am,9:00 am,10:00 am,11:00 am,12:00 pm,1:00 pm,2:00 pm,3:00 pm,4:00 pm,5:00 pm,6:00 pm,7:00 pm,8:00 pm,9:00 pm,10:00 pm,11:00 pm

• The second cell ("to" time) currently has the same list of items data validation set up

However, it's not realistic for the user to select a "to" time that is before the "from" time. Therefore, I wonder is it possible to code the data validation for the "from" time to consider the "to" time that they've previously selected?

For example: if the user selects 4:00pm as the "from" time, they can only select from 5:00 pm,6:00 pm,7:00 pm,8:00 pm,9:00 pm,10:00 pm,11:00 pm for the "to" time?

Answer 1

I used numbers in this example. In column C I have inserted the list we need and, in column D, I have inserted a filter with:

=arrayformula( ifna ( filter ( C2:C, C2:C > A2 ),""))

Finally, I validated cell B2 with

=$D$2:$D

as "list by range"

BTW: here a sheet based on hours