Data compiling formula

sheetA
A B C
data1 data 1a
data2 data 2a number
data3 data 3a
data4 data 4a number
data5 data 5a

sheetB
A B C
data2 data 2a number
data4 data 4a number

There are 2 worksheets above. I want data in Sheet A to appear in Sheet B if
any cells in collumn C has a number.
To make things more interesting, I have several "Sheet A" with different
sheet names.
Please help
Thanks.
--
Regards
FangYR
Malaysia

Use Autofiler:

1. insure that there is a header row over your data
2. select the cells in the header rown and pull-down: Data > Filter >
Autofilter
3. click the column C header diamond and select Non-blank

This will surpress the rows with blanks in column C

4. copy and paste into your second sheet

Note: you can always go back and remove the autofilter in the first sheet.
--
Gary''s Student


"FangYR" wrote:

> sheetA
> A B C
> data1 data 1a
> data2 data 2a number
> data3 data 3a
> data4 data 4a number
> data5 data 5a
>
> sheetB
> A B C
> data2 data 2a number
> data4 data 4a number
>
> There are 2 worksheets above. I want data in Sheet A to appear in Sheet B if
> any cells in collumn C has a number.
> To make things more interesting, I have several "Sheet A" with different
> sheet names.
> Please help
> Thanks.
> --
> Regards
> FangYR
> Malaysia

Thanks Gary's Student,
Can it be done automatically, I mean, as I enter data in sheetA, and if
there is a number in any cell in col C,sheetA, it will appear in sheet B?
--
Regards
FangYR
Malaysia


"Gary''s Student" wrote:

> Use Autofiler:
>
> 1. insure that there is a header row over your data
> 2. select the cells in the header rown and pull-down: Data > Filter >
> Autofilter
> 3. click the column C header diamond and select Non-blank
>
> This will surpress the rows with blanks in column C
>
> 4. copy and paste into your second sheet
>
> Note: you can always go back and remove the autofilter in the first sheet.
> --
> Gary''s Student
>
>
> "FangYR" wrote:
>
> > sheetA
> > A B C
> > data1 data 1a
> > data2 data 2a number
> > data3 data 3a
> > data4 data 4a number
> > data5 data 5a
> >
> > sheetB
> > A B C
> > data2 data 2a number
> > data4 data 4a number
> >
> > There are 2 worksheets above. I want data in Sheet A to appear in Sheet B if
> > any cells in collumn C has a number.
> > To make things more interesting, I have several "Sheet A" with different
> > sheet names.
> > Please help
> > Thanks.
> > --
> > Regards
> > FangYR
> > Malaysia

"FangYR" wrote:
> .. Can it be done automatically, I mean, as I enter data in sheetA, and if
> there is a number in any cell in col C, sheet: A, it will appear in sheet:
B?

Here's a non-array formulas play which delivers exactly what's wanted ..

A sample construct is available at:
http://cjoint.com/?cenbWhjJHG
Data compiling formula_FangYR_wks.xls

Source data is assumed in sheet: A, cols A to C, from row1 down

In sheet: B,

Put in A1:
=IF(ISERROR(SMALL($D:$D,ROW(A1))),"",
INDEX(A!A:A,MATCH(SMALL($D:$D,ROW(A1)),$D:$D,0)))
Copy A1 to C1

Put in D1:
=IF(A!C1="","",IF(ISNUMBER(A!C1),ROW(),""))

Select A1:D1, fill down to cover the max expected extent of data in sheet: A
Cols A to C will return the desired results from sheet: A
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---