HELP! Formula Nesting

[NX555V]

Hello All,

Thank in advance.

I have 2 dropdowns (validation list). One dropdown contains a list of "first names" 10 of them, the second contains a list of "last names" 10 of them also. When someone chooses a combination of "john" and "smith" (for example) from the individual dropdowns then cell A2 returns the value of "good student". The word "good student" is located on another sheet, I just want to point to it. Sheet2!A50 (for example). If someone chooses the combination "jane" and "wilson" then it will return the value "bad student" from another sheet.

I need to be able use the formula in 1 cell. That cell value continues to change as people choose different "first" and "last" names.

What is a good formula to accomplsih this?

 

[Stormseed]

I am sorry but I did not understand your question thoroughly.

The drop-down list have 2 values picked up from a range. Is this range at a constant ? I mean, if the 2 ranges have values, John and Smith, are they static or do these values keep changing in the range ?

 

[Gettingbetter]

Im guessing what your after here,

In this sum you have a validation dropdown in B5 and a validation dropdown in D5 (your first and last names). You also have a list of names in sheet2 column A, with good student, bad student etc in column B.

=IF(ISERROR(VLOOKUP(B5&" "&D5,Sheet2!A:B,2,FALSE)),"No student of that name",VLOOKUP(B5&" "&D5,Sheet2!A:B,2,FALSE))

What the formula is saying, is if theres an error finding the students name then its not in your list in sheet2, therefor put "No student of that name", if there isnt an error then it looks for the student in column A of sheet2 and brings back what is written in column B of sheet2

Hope this helps, you will obvioulsy have to change B5 and D5 to the cells that have your validation dropdown on them.

Cheers
GB