Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Creating a new formula
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? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Creating a new formula
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? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Creating a new formula
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? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Creating a new formula
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Creating charts from formula results | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
need help creating formula based on cell value | Excel Discussion (Misc queries) | |||
Creating a specific formula | New Users to Excel |