Data compiling formula

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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?

 

[Max]

.. 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 A11, 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

---