Make a formula return a different text when I click on a cell outside of a specified range. (??)

[edge37]

Hello! Given the sample included, I use a VBA code to show the active cell value in a cell (cell AM5), also I need to use the formula:

=IF(AM5>=91,"Advanced",IF(AM5>=80,"Excellent",IF(AM5>=70,"Needs improvement",IF(AM5<70,"Invalid"))))

so when I click in a name of a student, it returns one of the texts included in the formula based on their grades. The thing is that if I click a blank cell or any other one that is not any of the student's names, it still shows the text "Invalid" because a blank cell is also less than 70.

Is there a way to include something to limit this formula to work only in a range of specified cells (only when i click any student's name) and, if I click another cell outside that range, a specified new text would appear (like "name?" or whatever I choose) in the target cell. Can't figure out this one.

Thanks, and I hope I could describe my problem clearly.

 

[kevin9999]

=Try wrapping the VLOOKUPs in an IFERROR (see below). Then both formulas seem to work with your private sub. Of the 2 formulas suggested, I would go with @Scott Huish 's.

Book1
	A	B											M	N	O	P	Q	R	S	T					Y	Z	AA	AB
1
2
3																									FORMULAS
4
5
6
7																										name?	kevin9999's formula
8	#	STUDENT											10	10	X	0.2	0.45	1
9	1	ETT											0	0		0	33	33		$A$1						STUDENT?	Scott Huish's Formula
10	2	E5YEH											0	0		0	22	22
11	3	EG55EG											0	0		0	44	44
12	4	G5E											0	0		0	55	55
13	5	DFER											0	0		0		0
14
Sheet1

Cell Formulas
Range	Formula
Y5	Y5	=IFERROR(VLOOKUP($T$8,$B$9:$R$15,15,FALSE),"")
Z5	Z5	=IFERROR(VLOOKUP($T$8,$B$9:$R$15,16,FALSE),"")
AA5	AA5	=IFERROR(VLOOKUP($T$8,$B$9:$R$15,17,FALSE),"")
Z7	Z7	=IF(AA5<>"",IF(AA5>=91,"Advanced",IF(AA5>=80,"Excellent",IF(AA5>=70,"Needs improvement",IF(AA5<70,"Invalid")))),"name?")
T9	T9	=CELL("address")
Z9	Z9	=IF(AA5="","STUDENT?",LOOKUP(AA5,{0,70,80,91;"Invalid","Needs Improvement","Excellent","Advanced"}))
P8	P8	=SUM(C8:N8)/100
P9:P13	P9	=(C9+D9+E9+F9+G9+H9+I9+J9+K9+L9+M9+N9)
R9:R13	R9	=SUM(P9:Q9)

 

[edge37]

UPDATE I made a tweak on how the value of cell AM5 gets its value and now the formulas work. Thank you so much for your help

 

[edge37]

=Try wrapping the VLOOKUPs in an IFERROR (see below). Then both formulas seem to work with your private sub. Of the 2 formulas suggested, I would go with @Scott Huish 's.

Book1
	A	B											M	N	O	P	Q	R	S	T					Y	Z	AA	AB
1
2
3																									FORMULAS
4
5
6
7																										name?	kevin9999's formula
8	#	STUDENT											10	10	X	0.2	0.45	1
9	1	ETT											0	0		0	33	33		$A$1						STUDENT?	Scott Huish's Formula
10	2	E5YEH											0	0		0	22	22
11	3	EG55EG											0	0		0	44	44
12	4	G5E											0	0		0	55	55
13	5	DFER											0	0		0		0
14
Sheet1

Cell Formulas
Range	Formula
Y5	Y5	=IFERROR(VLOOKUP($T$8,$B$9:$R$15,15,FALSE),"")
Z5	Z5	=IFERROR(VLOOKUP($T$8,$B$9:$R$15,16,FALSE),"")
AA5	AA5	=IFERROR(VLOOKUP($T$8,$B$9:$R$15,17,FALSE),"")
Z7	Z7	=IF(AA5<>"",IF(AA5>=91,"Advanced",IF(AA5>=80,"Excellent",IF(AA5>=70,"Needs improvement",IF(AA5<70,"Invalid")))),"name?")
T9	T9	=CELL("address")
Z9	Z9	=IF(AA5="","STUDENT?",LOOKUP(AA5,{0,70,80,91;"Invalid","Needs Improvement","Excellent","Advanced"}))
P8	P8	=SUM(C8:N8)/100
P9:P13	P9	=(C9+D9+E9+F9+G9+H9+I9+J9+K9+L9+M9+N9)
R9:R13	R9	=SUM(P9:Q9)

It works wonderful, thanks again and again

 

[kevin9999]

Glad we could help & thanks for the feedback