automatically choose from dropdown menu by referencing cell values
Working at a university, I need to enter grades of my students in the school provided the excel sheet. There is process which I am sure can be performed automatically but none of my colleagues knew of it. I googled for a couple of days during my free time but was not very helpful given my shallow knowledge about excel.
What I need to do is the following:
(See the attached "sample.xls")
If the value of the column E (grade input) is above some threshold (say 40) then, the column F (exam board note, drop-down menu) becomes automatically P [Pass], otherwise it is left unchanged.
(Think that there are 200 students! And P [Pass] is located down below so I need to scroll down to be able to click it. This is really annoying and unproductive task!)
There are constraints: From the original excel file, (1) columns H and I (yellow coloured) are invisible. It was visible only when I pasted it into another excel sheet. Obviously they constructed the dropdown menus -column F and G from these columns and hid them by some tricks , and (2) the original file is password protected. (3) I want to maintain this stupid dropdown menu so that I don't get annoyed (and annoying) email from admin staffs that he/she cannot upload it to the system because of the reason they don't know. Absent these constraint, I could achieve my objective my using a function like " IF(cell>40, reference to the cell whose value is P [Pass]. ".
Any feedback/thoughts would be greatly appreciated.
Re: automatically choose from dropdown menu by referencing cell values
Is it feasible for you to set the first one to P [Pass] then copy it and paste it into the cells below it down as far as the last student row?
This won't destroy the Data Validation because the source cell and the destination cells have the same Data Validation.
Then all you need to do is change the ones that haven't passed.
Maybe you could add conditional formatting to highlight the cells that shouldn't show P [Pass] [Formula Is: =AND($E15<40,$F15="P [Pass]"]