HELP! Formula Nesting

NX555V

Board Regular
Joined
Feb 27, 2007
Messages
159
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

Banned
Joined
Sep 18, 2006
Messages
3,274
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

Well-known Member
Joined
Oct 12, 2004
Messages
602
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
 

Forum statistics

Threads
1,085,249
Messages
5,382,573
Members
401,795
Latest member
Ginger12

Some videos you may like

This Week's Hot Topics

Top