Custom data validation list

Hi all

I've had a search through the forum and can't seem to see anything quite like this problem, I'm hoping one of you can help!

I'm helping out a teacher friend of mine who is using a spreadsheet to track his students progress throughout the year. In each term the kids are assessed and given a 'rating'. I've currently got these ratings sat in a named range on a different sheet (with the weakest rating at the top and strongest at the bottom) and I'm using data validation to get these to appear in drop down lists. What would save my friend a bit of time is if this list could be altered slightly based on what the rating was in the previous term.

So, for instance, if in the Winter term they are given a '1b' rating, then the Spring term drop down list should start at 1b, either by excluding the data above it in the named range or by the drop down list starting at 1b when you click into the box and then the user can scroll up or down. (It doesn't matter which, I'm told the ratings never move backwards.)

I was hoping to achieve this through formula as I'm not sure what kind of security the school's network has in regard to macro enabled workbooks.

I hope this all makes sense, however if you have any queries please let me know. As always, many thanks in advance.

Jon

Hi all

I think I've solved it. I'm adding the following into the data validation source box:

=INDIRECT("Admin!"&ADDRESS(MATCH(O4,Ratings,0),COLUMN(Ratings))&":"&"$A$45")

Where: Admin contains the lookup, 'Ratings' contains the list, O4 is the rating from the previous term and A45 is the last entry on the ratings list.

This is working fine for me. Thanks to those who had a look at the problem!

Jon