Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move data from a sheet to another
Hello,
The sheet I have has 5 columns and the 5th is for status, IN or OUT. What I want is to MOVE the row to another worksheet in the same workbook IF the status is OUT. TIA Hernan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move data from a sheet to another
Hi Hernan
You can use Autofilter on the column and copy the visible data to another sheet Activate AutoFilter: Select a cell in your data table and use DataFilterAutoFilter to activate AutoFilter. Tip: Shortcut for the English version is Alt d f f In each header cell a dropdown will appear next to your field name. Click on the dropdown in the status field and choose OUT. Copy the filter result : 1) Be sure that the active cell is in the data range 2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK 3) Ctrl c or EditCopy 4) InsertWorksheet 5) Ctrl v or EditPaste 6) Select the sheet with the filter 7) Press Esc 8) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter Or use my filter add-in http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Hernan" wrote in message ... Hello, The sheet I have has 5 columns and the 5th is for status, IN or OUT. What I want is to MOVE the row to another worksheet in the same workbook IF the status is OUT. TIA Hernan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move data from a sheet to another
You could use formulas in 5 columns on the target sheet that would show the
information if status is OUT Assuming your source sheet is named "Source"(no quotes) In A1 of target sheet =IF(Source!$E1="OUT",Source!A1,"") Drag across to E1 and down to wherever. To actually "move" the data without formulas would require VBA Gord Dibben MS Excel MVP On Sun, 3 Dec 2006 10:55:01 -0800, Hernan wrote: Hello, The sheet I have has 5 columns and the 5th is for status, IN or OUT. What I want is to MOVE the row to another worksheet in the same workbook IF the status is OUT. TIA Hernan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move data from a sheet to another
If you do not want any blank cells in between, then try this:
In Sheet 2 A1 =IF(ISERR(SMALL(IF(Sheet1!$E$1:$E$7="Out",ROW(INDI RECT("1:"&ROWS(Sheet1!$A$1:$A$7)))),ROWS($1:1)))," ",INDEX(Sheet1!$A$1:$A$7,SMALL(IF(Sheet1!$E$1:$E$7 ="Out",ROW(INDIRECT("1:"&ROWS(Sheet1!$A$1:$A$7)))) ,ROWS($1:1)))) Adjust your range to suit ctrl+shift+enter (not just enter) copy down as far as need "Hernan" wrote: Hello, The sheet I have has 5 columns and the 5th is for status, IN or OUT. What I want is to MOVE the row to another worksheet in the same workbook IF the status is OUT. TIA Hernan |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move data from a sheet to another
I have not tried this, but I am assuming it works and great! :-)
However, what I am looking for is to do this automagically, so most likely this will have to be done with VBA. Sorry for my late response, you guys are great! Thanks for the great help. Hernan. "Teethless mama" wrote: If you do not want any blank cells in between, then try this: In Sheet 2 A1 =IF(ISERR(SMALL(IF(Sheet1!$E$1:$E$7="Out",ROW(INDI RECT("1:"&ROWS(Sheet1!$A$1:$A$7)))),ROWS($1:1)))," ",INDEX(Sheet1!$A$1:$A$7,SMALL(IF(Sheet1!$E$1:$E$7 ="Out",ROW(INDIRECT("1:"&ROWS(Sheet1!$A$1:$A$7)))) ,ROWS($1:1)))) Adjust your range to suit ctrl+shift+enter (not just enter) copy down as far as need "Hernan" wrote: Hello, The sheet I have has 5 columns and the 5th is for status, IN or OUT. What I want is to MOVE the row to another worksheet in the same workbook IF the status is OUT. TIA Hernan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
How to move data from one sheet to another | Excel Discussion (Misc queries) | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions | |||
Multiple worksheet queries | Excel Worksheet Functions |