I have a worksheet for active employees in one department and another sheet
for active employees in another department. Now these will remain in their
distinctive active sheet until their active status is change from Y to N.

I have created a third sheet where I would like excel to immediately
recognize the change from y to n on any of the active sheets and populate the
same info on the new row except with a changed "N" for inactive status.

Is this possible?