Data Validation Shrinking Choices - Microsoft Community

i'm working on data validation excel , i'm aware of how shrink available choices once 1 selected. i'm going though time tracker , if select 9 start time, end time have after 9 in drop down, though starts @ 7 am. want choices between 7 - 9 gone. goes next time put in. should start after end time of previous task. there way excel can that?

you have made me think hard on one.

it necessary create dynamic named range starts @ value after start selection , finishes end of list of times.

i created list of times @ 30min intervals on same sheet dropdowns can post snippet , try explain procedure. 2 yellow cells dropdowns. cell c1 contains start time dropdown , cell e1 contains finish time dropdown.

• create dynamic named range. called name finish. formula named range as below where:
• sheet1!$a$1   first cell of list of times  (must start @ row 1 otherwise additional difficulties introduced adjusting row numbers following match , offsets)
• sheet1!$c$1   cell containing value selected the start dropdown
• sheet1!$a$1:$a$48   list containing list of times

=offset(sheet1!$a$1,match(sheet1!$c$1,sheet1!$a$1:$a$48,0),0,counta(sheet1!$a$1:$a$48)-match(sheet1!$c$1,sheet1!$a$1:$a$48,0),1)

Office / Excel / Windows other / Office 2010