+ Reply to Thread
Results 1 to 3 of 3

automatically choose from dropdown menu by referencing cell values

  1. #1
    Registered User
    Join Date
    08-30-2011
    Location
    Belfast, NI
    MS-Off Ver
    Excel 2007
    Posts
    2

    automatically choose from dropdown menu by referencing cell values

    Greetings!

    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.

    Thanks in advance!
    Attached Files Attached Files
    Last edited by cesare; 08-30-2011 at 04:51 PM.

  2. #2
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    157

    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]"]

    Beau Nydal

  3. #3
    Registered User
    Join Date
    08-30-2011
    Location
    Belfast, NI
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: automatically choose from dropdown menu by referencing cell values

    Dear Beau Nydal,

    Quote Originally Posted by beaunydal View Post
    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?
    l
    You are a genius! You suggestion works like a charm!
    Thank you so much.

    -cesare

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1