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
 
Ad

Advertisements

Ad

Advertisements

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
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top