Look for correct line in other worksheets and return fail if any of them contain it

[nssbob]

hello

i am stumbling with this issue: i have sheet like this, on the device tabs i am filling Fails and Passes and i need a formula for the main tab that will check the other sheets and return fail if any of them contain fail or pass if all are passes (or not present) for each language into main sheet, not all the issues from the main sheet appear on all the device sheets.

is there a way to do this please?

 

[awoohaw]

You can probably do this effectively with POWER QUERY.
Here is a good video on what Power Query can do.

 

[nssbob]

thank you ill check it out

 

[nssbob]

that completly messed up with my head, i guess i am too stupid xD but i did managed to figure out a solution in the end if anyone will ever face similar problem i managed to sort it out in following way

=IF(OR(IFERROR(VLOOKUP(B3,device1!$B$3:$E$10,2,FALSE), "pass")="fail", IFERROR(VLOOKUP(B3,device2!$B$3:$E$10,2,FALSE), "pass")="fail", IFERROR(VLOOKUP(B3,device3!$B$3:$E$10,2,FALSE), "pass")="fail", IFERROR(VLOOKUP(B3,device4!$B$3:$E$9,2,FALSE), "pass")="fail"), "Fail", "Pass")