excel dynamic date validation

Question

I have a long list of dates to use in a drop down on an Excel spreadsheet that is being used as a User Form to collect data.  Currently the User selects the cell with the data validation dropdown and all the dates show as expected as the cell is validated to show all the dates in the list.  There is another cell on the form which acts as a start date. 

Is it possible to show dates in the drop down that start from the 'Start Date' and not from the beginning of the long list.  I can do this using a macro but do not wish to have any macros in this workbook.

Any ideas will be gratefully recieved.

very best regards

Spike

Answer 1

Hi

Please, try the formula for the Data Validation list

=INDIRECT("Sheet1!$A"&MATCH(Sheet1!$E$1,Sheet1!$A$1:$A$32,0)&":$A$32")

RESULTS

Do let me know if you need more help

On the other hand,

If the answer helped you.

Please, consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance

Regards

Jeovany

Answer 2

The answer i have got to work is as in the fomula below to create a dynamic range then use that in the validation as a list

INDEX(Sheet1!A1:A32,MATCH(Sheet1!E1,Sheet1!A1:A32,0),1):Sheet1!$A$32

where E1 is the start date and A1:A32 is the list of dates

Spike

Answer 3

Many thanks for that. 

Sorry i was not very clear; i am not using a VBA user form or any vba at all. Reason being if the file is an .xlsm then all sorts of issues arise when users not familiar with macros open it! Hence i need to avoid any macros which is a shame as easy to do with a macro.

very best regards

Spike

Answer 4

Hi Spike 

If you are already using VBA Userform that obviously should have macros.

I don't understand in what way will one more macro could not  e used to solve your problem?

Regards

Jeovany