Range Lookup and return certain text

[Simmy1982]

This seams like it would be super easy and for what ever reason its slipping my mind. And I am sorry that I can't use XL2BB as the permissions are blocked on my work computer so I have to resort to attaching snip its of a smaller version of what I am trying to do.

What I am trying to do is look at sheet 2 D-F for "Name x" and return text to sheet 1 for "Name x". If the person is listed as absent for any task on sheet 2 then return Absent to sheet 1 for that person, then look for Not Complete on any task and return that to sheet 1 and then if all tasks are marked complete then sheet 1 would return Complete. It is only one value to return and in the order of "Absent", "Not Complete" and then "Complete".

 

[etaf]

ignore - got wrong

i'll setup and see if works

 

[etaf]

well , this is not a great formula
=IF(COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$B$2:$B$100,"complete",Sheet2!$C$2:$C$100,"complete",Sheet2!$D$2:$D$100,"complete",Sheet2!$E$2:$E$100,"complete",Sheet2!$F$2:$F$100,"complete")=1,"complete",IF((COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$B$2:$B$100,"not complete")+COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$C$2:$C$100,"not complete")+COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$D$2:$D$100,"not complete")+COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$E$2:$E$100,"not complete")+COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$F$2:$F$100,"not complete"))>0,"Not Complete",IF((COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$B$2:$B$100,"absent")+COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$C$2:$C$100,"absent")+COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$D$2:$D$100,"absent")+COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$E$2:$E$100,"absent")+COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$F$2:$F$100,"absent"))>0,"Absent","other")))

i'm sure there are better ways

Book3
	A	B
1
2	name1	complete
3	name2	Not Complete
4	name3	Not Complete
5	name4	Not Complete
6	name5	Not Complete
7	name6	other
8	name7	Not Complete
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=IF(COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$B$2:$B$100,"complete",Sheet2!$C$2:$C$100,"complete",Sheet2!$D$2:$D$100,"complete",Sheet2!$E$2:$E$100,"complete",Sheet2!$F$2:$F$100,"complete")=1,"complete",IF((COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$B$2:$B$100,"not complete")+COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$C$2:$C$100,"not complete")+COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$D$2:$D$100,"not complete")+COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$E$2:$E$100,"not complete")+COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$F$2:$F$100,"not complete"))>0,"Not Complete",IF((COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$B$2:$B$100,"absent")+COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$C$2:$C$100,"absent")+COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$D$2:$D$100,"absent")+COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$E$2:$E$100,"absent")+COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$F$2:$F$100,"absent"))>0,"Absent","other")))

Book3
	A	B	C	D	E	F
1
2	name1	complete	complete	complete	complete	complete
3	name2	not complete	not complete	not complete	not complete	not complete
4	name3	abscent	complete	not complete	not complete	not complete
5	name4	not complete	complete	not complete	not complete	not complete
6	name5	not complete	abscent	not complete	abscent	abscent
7	name6	complete	complete	complete	complete
8	name7	not complete	abscent	not complete	not complete	not complete
9	name8	not complete	not complete	not complete	not complete	not complete
10	name9	not complete	not complete	not complete	not complete	not complete
11	name10	not complete	not complete	not complete	not complete	not complete
Sheet2

this will be on dropbox for just a few days only

return value based on table count - ETAF.xlsx

Shared with Dropbox

www.dropbox.com