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
---