Select Columns based on cell value

[Guest]

Hoping I can explain this correctly. I have multiple
spreadsheets in the same workbook that are all the same
but contain different information. I am trying to get a
seperate sheet to look at all other worksheets and if a
cell contains a certain value, then it will copy that
column into the new sheet.

Example Data: This is the column view of two sheets
SHEET1
ColA ColB ColC ColD
Red Blue Red Green
1 2 3 4

SHEET2
ColA ColB ColC ColD
Blue Red Blue Green
1 6 3 4


I would like SHEET3 to contain the columns above that
contain red in the first cell so new sheet would look like:

ColA ColB ColC
Red Red Red
1 3 6

Is there a way to do this programatically? If so any
assistance would be appreciated.

 

[Edwin Tam (MS MVP)]

Sorry, could you please explain the logic? Why in sheet3, Column A = 1, Column B = 3, Column C = 6?

I guess Column A = 1, Column B = 6, Column C = 3... If so, I understand. If not, please explain the logic.

Anyway, assuming what I though is correct. The following custom function can solve your problem.

'------------------------------------------------------------------------
Function SUMIF3D(ByVal sum_range As range, ByVal compare_range As range, ByVal find_value)
Dim s As Object, tmp$, tmp2$
Application.Volatile
tmp = sum_range.Cells(1).Address(False, False)
tmp2 = compare_range.Cells(1).Address(False, False)

For Each s In sum_range.Parent.Parent.Worksheets
If s.Name <> sum_range.Parent.Name Then
If s.range(tmp2).Value = find_value Then
SUMIF3D = Application.Sum(s.range(tmp)) + SUMIF3D
End If
End If
Next
End Function
'---------------------------------------------------------------

sum_range = the address of the cell you want to pick out in all worksheets. (A2 in your example)
compare_range = the address of the cell you want to compare in all worksheets. (A1 in your example)
find_value = the value which, if found in "compare_range", pick out the corresponding value in "sum_range" (RED in your example)

In Sheet3, cell A1, you type "Red".
In cell A2, you type:
=SUMIF3D(A2,A1,A1)

Then, you can drag the formula across to the cells on the right.

You may note the, instead of just "picking the values", it sums the values which satisfy the criteria.

Regards,
Edwin Tam
(e-mail address removed)



----- (e-mail address removed) wrote: -----

Hoping I can explain this correctly. I have multiple
spreadsheets in the same workbook that are all the same
but contain different information. I am trying to get a
seperate sheet to look at all other worksheets and if a
cell contains a certain value, then it will copy that
column into the new sheet.

Example Data: This is the column view of two sheets
SHEET1
ColA ColB ColC ColD
Red Blue Red Green
1 2 3 4

SHEET2
ColA ColB ColC ColD
Blue Red Blue Green
1 6 3 4


I would like SHEET3 to contain the columns above that
contain red in the first cell so new sheet would look like:

ColA ColB ColC
Red Red Red
1 3 6

Is there a way to do this programatically? If so any
assistance would be appreciated.