Using Data Validation list formula

[sailor_taylor]

I'm trying to use Validation > List to allow my sister to easily input scores into her school gradebook.

Cell B1 contains the total possible correct answers for a test. This number changes every week.

Cell C1 is where she will input the number of correct answers from Student 1. I would like to have an in-box drop-down arrow limited from 0 (zero) to whatever number is in the aforementioned cell B1 (the maximum possible score.)

I assume I need a formula to insert in the Data Validation > Settings > Source box. (If that route is different from yours, it is probably because I'm using Excel 2008 for Mac).

Thank you for your time, and Happy Thanksgiving. Mike

 

[T. Valko]

I'm trying to use Validation > List to allow my sister to easily input scores into her school gradebook.

Cell B1 contains the total possible correct answers for a test. This number changes every week.

Cell C1 is where she will input the number of correct answers from Student 1. I would like to have an in-box drop-down arrow limited from 0 (zero) to whatever number is in the aforementioned cell B1 (the maximum possible score.)

I assume I need a formula to insert in the Data Validation > Settings > Source box. (If that route is different from yours, it is probably because I'm using Excel 2008 for Mac).

Thank you for your time, and Happy Thanksgiving. Mike

Try this...

In some out of the way location list the sequence of numbers 0 to the max number you think you'll need. For example, if the max number that will ever be needed is 20 then in the range J1:J21 enter the number sequence 0 to 20.

Then, as the source for the drop down use this formula:

=IF(B1,J1:INDEX(J1:J21,B1+1),#N/A)

If cell B1 is empty (or contains a text entry) when you set the validation you'll get a message something like: The source currently evaluates to an error. Do you want to continue?. Just answer YES.

 

[sailor_taylor]

Thanks so much Biff! Your solution works like a charm. Have a great holiday season.
Best regards,
Mike

 

[T. Valko]

Thanks so much Biff! Your solution works like a charm. Have a great holiday season.
Best regards,
Mike

You're welcome. Thanks for the feedback!