DIFFICULT -

[Guest]

In one file I have multiple rows and columns of data.

In the second file I am trying to write a CSE(Array Formula) which will print values into column A of file2. It should:
Look at all the values in file1!B2:B200
If the value in the file1!B column is "high" then it should print the exact text of the corresponding file1!E column into the next available slot in the A column of file2.

Example:

in file1
b1="low" ----e1="apples"
b2="high" ----e2="oranges"
b3="low" ----e3="pears"
b4="high" ----e4="grapes"
. . .


then I would like a formula that inputs in file2 column A and returns
oranges in A1
grapes in A2
. . .

Thanks.

 

[GaryB]

This is a bit crude, but I think it gets you where you need to be. I can't think of a formula to do what you want as it would need to keep pointing at different cells, but try adding this to your File2 and running it.


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 27/02/02 by GaryB
'


Dim myrow As Integer
Dim myrow2 As Integer
Dim RowEtext As String


myrow = 1
myrow2 = 0

For myrow = 1 To 200

Windows("file1.xls").Activate
Range("A1").Select
If ActiveCell.Offset(myrow - 1, 1) = "high" Then
RowEtext = ActiveCell.Offset(myrow - 1, 4)
Windows("file2.xls").Activate
Range("A1").Select
ActiveCell.Offset(myrow2, 0) = RowEtext
myrow2 = myrow2 + 1
End If
Next myrow

'
End Sub


HTH

GaryB

 

[Aladin Akyurek]

Gary,

That seems to me the right approach to the OP's question.

Aladin