4

I currently have a Data validation of Custom formula on a cell, B8, that looks a little like this:

=or(and(B7="Yes",isblank(B8)),and(B7="No",B8>=75,B8<=100))`

I'd like to turn the latter validation of B8>=75,B8<=100 into a drop-down selection with the numbers 75-100—is that possible? I only know you can with the List of items option.

2

This can be done using List from a range criterion.

On another sheet, say Sheet2, enter conditional formulas that will be either blank or a number, based on B7 cell. Like this, entered in cells A1:A26 of Sheet2:

=if(Sheet1!$B$7="Yes",,row()-row(A$1)+75)

This will be either blank or the numbers 75..100.

In B8 of Sheet1, use data validation "List from a range": Sheet2!A1:A26 . This will give you in-cell dropdown, which will be either empty or contain numbers 75..100, depending on what's in B7 of Sheet1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.