Update a cell with drop-down values based on cell values within a range.

I have a cell that has drop-down values that I would like to be updated based on values in a specific range. So, if a cell has "yes" then a specific text would appear in the cell that has the drop-down list.

I have an If formula that works just fine when the cell doesn't have a drop-down value, but need it to work in a cell that does have a drop-down value. Any suggestions?

This is my If formula
=IF(["at symbol"[Year 7]]="Yes","Year 7",IF(["at symbol"[Year 8]]="Yes","Year 8",IF(["at symbol"[Year 9]]="Yes","Year 9",IF(["at symbol"[Year 10]]="Yes","Year 10",IF(["at symbol"[Year 11]]="Yes","Year 11",IF(["at symbol"[Year 12]]="Yes","Year 12",""))))))

NB: "at symbol" is literally the "at" symbol, but i wasn't allowed to post with that symbol in the formula.

In the above example:
Cell G2 would equal "Year 8" because cell B2 equals "Yes"
Cell G3 would equal "Year 10" because cell D3 equals "Yes"

	A	B	C	D	E	F	G
1	Year 7	Year 8	Year 9	Year 10	Year 11	Year 12	What Year are you in
2	No	Yes	No	No	No	No	Cell with drop-down list
3	No	No	No	Yes	No	No

Hi AliGW and menem
Maybe I have put my question in the wrong thread. I do have a formula that works, the problem is the cell I want to put it in won't allow me to enter the data. So even if both of your formulas worked, I still can't use it in the cell that has the set value list.
Any idea how I can implement a formula in a cell that is only prepared to accept values from a drop-down list?
My drop-down list is defined, I just need to tell the cell which value to select based on results in the range.
Thanks

It's related to a template being used to import and export data from a database.
OK. Understand, I can't add a formula to a cell that has a drop-down. That solves that problem then! I thought I must've been missing something.
Cheers

yeah, I hear what you are saying and probably agree with you. I was just using the template that the system downloads and it's kinda coded in to the excel spreadsheet.
I'll be able to work around it I think.
Thanks again for your time helping me.

AliGW - Thanks for your Forum advice to this forum newbie. LOL