I’m doing data validation on a group of cells. At the moment all the names
of the employees appear on the drop down list for those cells, but what I
need is to compare those names to a day-off list where the people who are off
on that day appear. If their names appear on the day-off list then they
should not appear on my data validation cells.
In other words I have colA-colG [for] Mon-Sun.
I have 30 rows. The first 15 rows are for my data validation and they are
drop downs of the people who are working on that day. The source for those
rows comes from a separate sheet where all the names of my employees are
listed. Rows 16-30 have the names of the people who are off that day. So
the names in Rows 1-15 should not match 16-30.

The following seems to work with Excel 2003.
For convenience, I put complete employee list in J1:J15 of the same
sheet. Putting them on a different sheet is a straightforward change.

I use rows 31:46 as helper rows.

Leave row 31 empty.

In A32 put
=IF(COUNTIF(A$16:A$31,$J1)0,"",MAX(A$31:A31)+1)
Then copy A32 down to A46. Then select A32:A46 and copy rightward to
column G.

In A1 put
=IF(ROW()MAX(A$32:A$46),"",
OFFSET($J$1,MATCH(ROW(),A$32:A$46)-1,0))
Then copy A1 down to A15. Then select A1:A15 and copy rightward to
column G.

Modify to suit.