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?

I would recommend you add both of your lists together on one sheet, and
install one additional column titled DEPARTMENT.........then you can easily
separate the active/inactive/by Department by using the Data Filter
AutoFilter feature......

Vaya con Dios,
Chuck, CABGx3



"vanchi233" wrote:

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?

and i meant "sheet", sorry

"CLR" wrote:

I would recommend you add both of your lists together on one sheet, and
install one additional column titled DEPARTMENT.........then you can easily
separate the active/inactive/by Department by using the Data Filter
AutoFilter feature......

Vaya con Dios,
Chuck, CABGx3



"vanchi233" wrote:

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?

Here's one non-array formulas play
which could deliver exactly what's wanted ..

Sample construct available at:
http://www.savefile.com/files/7849807
Auto copy n stack rows from 2 sheets in new sheet based on status col.xls

Assume source data in 2 identically structured sheets: X, Y
cols A to E, data from row2 down.

The status col = col E,
wherein the "Y", "N" will be tagged for the lines in X and Y.

In another sheet: InActive
With the same headers pasted into A1:E1

Put in A2:
=IF(ISERROR(SMALL($G:$G,ROW(A1))),
IF(ISERROR(SMALL($H:$H,ROW(A1)-COUNT($G:$G))),"",
INDEX(Y!A:A,SMALL($H:$H,ROW(A1)-COUNT($G:$G)))),
INDEX(X!A:A,MATCH(SMALL($G:$G,ROW(A1)),$G:$G,0)))
Copy A2 to E2

Put in G2:
=IF(X!E2="","",IF(X!E2="N",ROW(),""))

Put in H2:
=IF(Y!E2="","",IF(Y!E2="N",ROW(),""))

(Leave G1:H1 empty)

Select A2:H2, copy down to cover
the aggregated max expected extent of data in X and Y
(eg: If X, Y contains max 10 rows each, copy down by 20 rows)

The above will automatically return only lines (from cols A to E) in both X
and Y where col E = "N", with lines from X stacked above those from Y. all
lines neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"vanchi233" wrote:
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?