Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
moving data depending on criteria
I completed my spreadsheet using the formulas previously given, however, I
was wondering if it is possible to delete the data automatically from the original sheet once it has been moved to the second sheet. Hiding the data would work so long as it is an automatic process. Many thanks in advance. "Max" wrote: Here's a formulas play which can deliver the automation you seek Assume source data is in a sheet: x, cols A to I, data from row2 down, with key col F & criteria values: 0 or 100 In another sheet: y (say), Place in A2: =IF(x!F2="","",IF(OR(x!F2={0,100}),ROW(),"")) Leave A1 blank Put in B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) Copy B2 to K2. Select A2:J2, copy down to cover the max expected extent of source data in x, say down to J500? Hide away col A. Cols B to J will return the required results, ie only the lines where col F contains either: 0 or 100, with all lines neatly bunched at the top. As inputs are made in x, y will automatically display the required lines. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
moving data depending on criteria
Extending from the earlier formulas set-up, you could actually auto-produce
the required "converse" list via a similar manner in another sheet (say, z) by just tweaking the criteria formula in col A Assume source data is in a sheet: x, cols A to I, data from row2 down, with key col F & criteria values: 0 or 100 In another sheet: z (say), Place in A2: =IF(x!F2="","",IF(OR(x!F2={0,100}),"",ROW())) Leave A1 blank Then put in B2, as befo =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) Copy B2 to K2. Select A2:J2, copy down to cover the max expected extent of source data in x, say down to J500? Hide away col A. Cols B to J will return the required results, ie the rest of the lines other than those where col F contains either: 0 or 100, with all lines neatly bunched at the top. As inputs are made in x, z will automatically display the required lines. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "harwookf" wrote: I completed my spreadsheet using the formulas previously given, however, I was wondering if it is possible to delete the data automatically from the original sheet once it has been moved to the second sheet. Hiding the data would work so long as it is an automatic process. Many thanks in advance. "Max" wrote: Here's a formulas play which can deliver the automation you seek Assume source data is in a sheet: x, cols A to I, data from row2 down, with key col F & criteria values: 0 or 100 In another sheet: y (say), Place in A2: =IF(x!F2="","",IF(OR(x!F2={0,100}),ROW(),"")) Leave A1 blank Put in B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) Copy B2 to K2. Select A2:J2, copy down to cover the max expected extent of source data in x, say down to J500? Hide away col A. Cols B to J will return the required results, ie only the lines where col F contains either: 0 or 100, with all lines neatly bunched at the top. As inputs are made in x, y will automatically display the required lines. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to move data to another worksheet depending on the criteria | Excel Discussion (Misc queries) | |||
move data to another sheet depending on criteria | Excel Worksheet Functions | |||
Populate, Depending on Criteria | Excel Discussion (Misc queries) | |||
Copying to Workbook depending on Criteria? | New Users to Excel | |||
Delete row depending on criteria | Excel Discussion (Misc queries) |