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 this:

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

 

[Scott Huish]

=IF(AM5="","?name",LOOKUP(AM5,{0,70,80,91;"Invalid","Needs Improvement","Excellent","Advanced"}))

 

[edge37]

Try this:

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

Thank you, unfortunatelly, when I tried it, it still won't work the way I want. When I click another cell that is not the range of names, it always says "invalid", doesn't show "name?"

 

[edge37]

=IF(AM5="","?name",LOOKUP(AM5,{0,70,80,91;"Invalid","Needs Improvement","Excellent","Advanced"}))

Thank you, unfortunatelly still don't work for me. Maybe there could be a way to define in which range of cells it will work and outside of that range it would show a defined text.

 

[Scott Huish]

How is this not doing what you want? If this isn't it, can you explain further?

Book1
	AM	AN
5	80	Excellent
6	75	Needs Improvement
7		?name
8	91	Advanced
Sheet1

Cell Formulas
Range	Formula
AN5:AN8	AN5	=IF(AM5="","?name",LOOKUP(AM5,{0,70,80,91;"Invalid","Needs Improvement","Excellent","Advanced"}))

 

[kevin9999]

We could benefit from seeing your entire code, as well as the actual sheet using the XL2BB add in, or alternatively if you could share your file via Google Drive, Dropbox or similar file sharing platform. Without that, I'll step out of this thread for now.

 

[edge37]

DUMMY.xlsm
	A	B											M	N	O	P	Q	R	S	T					Y	Z	AA	AB
3																									FORMULAS
4
5																									#N/A	#N/A	#N/A
6
7																										#N/A	kevin9999's formula
8	#	STUDENT											10	10	X	27%	45%	100%
9	1	ETT											0	0		3	33	36		$A$3						#N/A	Scott Huish's Formula
10	2	E5YEH											0	0		2	22	24
11	3	EG55EG											0	0		26	44	70
12	4	G5E											0	0		27	55	82
13	5	DFER											0	0		91		91
10A (1)

Cell Formulas
Range	Formula
Y5	Y5	=VLOOKUP($T$8,$B$9:$R$15,15,FALSE)
Z5	Z5	=VLOOKUP($T$8,$B$9:$R$15,16,FALSE)
AA5	AA5	=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)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Y4:AA7,Y3	Cell Value	=0	text	NO
C9:N15	Cell Value	=0	text	NO
C9:N15	Cell Value	=0	text	NO
C9:N15	Cell Value	=0	text	NO
C9:N15	Cell Value	=0	text	NO
C9:N15	Cell Value	=0	text	NO
C9:N15	Cell Value	=0	text	NO
C9:N15	Cell Value	=0	text	NO
C9:N15	Cell Value	=0	text	NO
C9:N15	Cell Value	=0	text	NO
C9:N15	Cell Value	=0	text	NO
C9:N15	Cell Value	=0	text	NO
C9:N15	Cell Value	=0	text	NO
C9:N15	Cell Value	=0	text	NO
B9:R15	Expression	=IF(AND($T$9 = "$B$" & ROW(B9), ROW(INDIRECT($T$9)) = ROW()), 1, 0)	text	NO
R9:R15	Expression	=ISODD(ROW())	text	NO
P9:Q15	Expression	=ISODD(ROW())	text	NO
M9:M15	Expression	=ISODD(ROW())	text	NO
A9:L15	Expression	=ISODD(ROW())	text	NO
O9:O15	Expression	=ISODD(ROW())	text	NO
N9:N15	Expression	=ISODD(ROW())	text	NO
R9:R15	Cell Value	<70	text	NO
AA5	Expression	=IF(ISBLANK(B1),"0","")	text	NO
Y5:Z5	Expression	=IF(ISBLANK(C2),"0","")	text	NO
Z5	Expression	=IF(ISBLANK(A1),"0","")	text	NO

 

[edge37]

I also use a VBA code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("T8").Value = ActiveCell.Value
End Sub

 

[edge37]

How is this not doing what you want? If this isn't it, can you explain further?

Book1
	AM	AN
5	80	Excellent
6	75	Needs Improvement
7		?name
8	91	Advanced
Sheet1

Cell Formulas
Range	Formula
AN5:AN8	AN5	=IF(AM5="","?name",LOOKUP(AM5,{0,70,80,91;"Invalid","Needs Improvement","Excellent","Advanced"}))

Thanks again, but I cannot make it work like I want it over here. maybe it is my lack of experience. From the mini sheet included above, when I click a student's name, the classification appears according to their grades in cell Z9, but I'd like this to work only in a range I can specify (student's names for example), so if I click any other cell, blank cell or else, outside that range, the phrase "name?" (or anything else I want) would appear instead.

Not sure if I could explain myself better this time.

thank you again