# Copy only specific information from one colum to another worksheet

B

#### Brandon

I am looking for a formula that will allow me to copy data from one
spreadsheet into another based on values within my initial worksheet.

For example, if I have 5 colums of data listed below and their corresponding
data;

A B C D E F
3 4 3 5 3 3

I only want to display data that is equal to 3 on another worksheet, so this
would appear as;

A B C
3 3 3

I am trying to find a formula that will allow me to pull across only these
values and exclude the other colums as they do not equal 5.

Thanks,
Brandon

B

#### Brandon

The last line should say the values do not equal "3".

M

#### Max

Venturing a thought as to the scenario you have, and what you're after
(I'm not sure from what you posted)

Assuming you have this data in Sheet1's A2:F2 down
(till say F100)

3 4 3 5 3 3
a b c d e f
1 2 3 4 5 6
7 8 9 10 11 12
etc

and that what you want to extract dynamically in another sheet is this,
ie show only the cols where the top* source row contains: 3,
a sort of horizontal filter viz:
*ie Sheet1's A2:F2

3 3 3 3
a c e f
1 3 5 6
7 9 11 12
etc

In another sheet,
In A1: =IF(Sheet1!A2="","",IF(Sheet1!A2=3,COLUMNS(\$A:A),""))
Copy A1 to F1

In A2:
=IF(COLUMNS(\$A:A)>COUNT(\$A\$1:\$F\$1),"",INDEX(Sheet1!\$A\$2:\$F\$100,ROWS(\$1:1),SMALL(\$A\$1:\$F\$1,COLUMNS(\$A:A))))
Copy A2 to F2, fill down to cover the max expected extent of source data.
That'll return the results as indicated above. Empty source cells, if any,
will show as zeros. For a neater look, you can switch off the display of
zeros in the sheet via Tools > Options > View tab, uncheck Zero values > OK
--
Max
Singapore
http://savefile.com/projects/236895