Or, index, match statements

[Unknown Soldier]

I have a workbook with 8 workheets. The first 7 worksheet are name Mon,
Tues, Wed, Thur, Fri, Sat, Sun. These 7 contain the name my employees and
whether they show up to work in text statement. They all look like these.

Mon Worksheet

A1 B1
Name
Tom
Jason not show up to work
Susan not Show up to work
Ryan
Bill not Show up to work

Notice that if any of my 5 employees show up to work, cell in column B is
leaving blank

Similarly for Tues

A1 B1
Name
Tom
Jason not show up to work
Susan not Show up to work
Ryan
Bill

From Wed to Sun are similar. Now, on the last worksheet in the workbook
which is the number 8 worksheet, I named it "contract cancel". I will have
the names of all my employees just like I did in the 7 weekdays worksheets.
However, in the B column, I want to have an if statement that check each of
my employees in 7 days, and if any of them have a statement not show up to
work, I want it to be display in column B of sheet "contract cancel". In
this way, I can say goodbye to to them because they were absence or did not
show up to work at least one day last week.

contract cancel worksheet

A1 B1
Name
Tom
Jason not show up to work
Susan not Show up to work
Ryan
Bill not show up to work

I know I must use the "if" statement, then "or" statement, then "index",
then "match", but I can't figure out how put them together or try to get it
to work. Can anyboday help?

 

[T. Valko]

One way...

Download and install the free add-in Morefunc.xll from:

http://xcell05.free.fr/english/

It contains a function called THREED that can be used in an array formula**
like this:

=SUM((THREED(Mon:Sun!A$2:A$10)=A2)*(THREED(Mon:Sun!B$2:B$10)="no show"))

A result that is >0 means that employee had a "no show" on some day of the
week.

Or, you can write the formula like this to get a result of "no show" :

=IF(SUM((THREED(Mon:Sun!A$2:A$10)=A2)*(THREED(Mon:Sun!B$2:B$10)="no
show")),"no show","")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Here's a sample file that demonstrates this. The Morefunc add-in functions
have been embeded in this file. For it to work properly you'll have to
enable macros. This is for demostration purposes only.

xTHREED.xls

http://www.freefilehosting.net/download/3ed74