Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create list based on criteria
Hello,
I want some data from another sheet to automatically populate in a new sheet. The sheet holding the data is in A19:I150 and it has the heading is the following: Date Desc Code No Dep Disb Balance GL Cleared Balance 100,000.00 1/1/10 ABC zxcv 10-01 -1500 98,500.00 Y N 1/2/10 CVB werv 10-02 -78.69 98,421.31 Y N 1/5/10 CVB zxcv 10-08 -10.00 98,411.31 N Y 1/6/10 PPP werc 1023 -5.00 98,406.31 N Y I would like the records marked "N" from the "Cleared" column to populate in a new sheet and display only the following information Date Desc No Disb Based on data provided in this example, it should populate 1/1/10 ABC 10-01 -1500 1/2/10 CVB 10-02 -78.69 Please help me, Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create list based on criteria
Assumptions:
Date Desc - Col A N - Col C Disp - col E Put this formula in col A in Sheet2 =IF(ROW(A1)<=COUNTIF(Sheet1!$C:$C,"N"),INDEX(Sheet 1!A:A,SMALL(IF(Shee1!$C$1:$C$200="N",ROW($A$1:$A$2 00)),ROW(A1))),"") Put this formula in col B in Sheet2 =IF(ROW(C1)<=COUNTIF(Sheet1!$C:$C,"N"),INDEX(Sheet 1!C:C,SMALL(IF(Shee1!$C$1:$C$200="N",ROW($C$1:$C$2 00)),ROW(C1))),"") Put this formula in col C in Sheet2 =IF(ROW(E1)<=COUNTIF(Sheet1!$C:$C,"N"),INDEX(Sheet 1!E:E,SMALL(IF(Shee1!$C$1:$C$200="N",ROW($E$1:$E$2 00)),ROW(E1))),"") CLick yes if helped -- Greatly appreciated Eva "Bexi" wrote: Hello, I want some data from another sheet to automatically populate in a new sheet. The sheet holding the data is in A19:I150 and it has the heading is the following: Date Desc Code No Dep Disb Balance GL Cleared Balance 100,000.00 1/1/10 ABC zxcv 10-01 -1500 98,500.00 Y N 1/2/10 CVB werv 10-02 -78.69 98,421.31 Y N 1/5/10 CVB zxcv 10-08 -10.00 98,411.31 N Y 1/6/10 PPP werc 1023 -5.00 98,406.31 N Y I would like the records marked "N" from the "Cleared" column to populate in a new sheet and display only the following information Date Desc No Disb Based on data provided in this example, it should populate 1/1/10 ABC 10-01 -1500 1/2/10 CVB 10-02 -78.69 Please help me, Thanks . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query criteria based on a changing criteria list | Excel Discussion (Misc queries) | |||
How to create multiple new workbooks based on sort criteria | Excel Discussion (Misc queries) | |||
Extract list of units based on error criteria to new list | Excel Worksheet Functions | |||
Extract list of units based on error criteria to new list | Excel Worksheet Functions | |||
Create a list based on single shared criteria | Excel Worksheet Functions |