automatically fill column with conditional data from other worksheet

[noot]

Hithere,

It can't be very difficult, but I can't find the right terminology to find
the solution myself through google.

Suppose:

I have an excel sheet with two tabs.
The first tab is filled with various values, the second tab is completely
empty.

I want the first column of the second (empty) tab populated automatically
with values from a particular column of the first tab if certain conditions
are met.

The condition is e.g. "larger than 5"

If in tab 1 column A is filled vertically with:

1
6
2
7
3
8
4
10

I would like column A of tab 2 automatically vertically filled with:

6
7
8
10

Where to begin?

Tia!

 

[L. Howard Kittle]

On the second sheet and pull down:

=IF(Sheet1!A1>5,Sheet1!A1,"")

HTH
Regards,
Howard

 

[noot]

Thnx for your fast reply Howard.
Yet your suggustion results in an error (Excel 2007) with the part:
5,Sheet1 accentuated in bold?

noot

 

[noot]

Solved it, it worked when replacing "," with ";"
(why would this be?)

Still I would like to refine the solution some more;

With this formula it results in:

A1: "blank"
A2: 6
A3: "blank"
A4: 7
A5: "blank"
A6: 8
A7: "blank"
A8: 10

I could resolve this with afterwards filtering the column smallest to
largest.

I would yet prefer to have a1, a2 a3 a4 etc. filled directly with only the
'met conditions':

A1: 6
A2: 7
A3: 8
A4: 10

And to ask even more, could I ommit the "pull down" method, and have Excel
automatically search in complete column containing data?

Tia!

 

[L. Howard Kittle]

Give this a try. I know there is a way to get the numbers to start in A1
and then offset from there but I am drawing a blank at present, hence this
is not a complete solution since the numbers start in A2. However, if you
have a header in A1, then this is what you will probably need.

Sub CopyFivers()
Dim Dum As Range, c As Range
Set Dum = Range("A:A")

For Each c In Dum
If c.Value > 5 Then
Sheets("Sheet2").Range("A100") _
.End(xlUp).Offset(1, 0) = c
End If
Next

End Sub

HTH
Regards
Howard

 

[noot]

Thnx Howard!

These 'codes' are quite new to me. It will take some time for me to figure
out how to implement it, but thnx in advance for this solution!

(if I don't figure it out i'll be back ;-)

 

[L. Howard Kittle]

Well, don't struggle with it too much.

If you have problems changing ranges and such to suit your sheet, post back
and I, or someone else, will be happy to try to get it working for you.

Regards,
Howard

 

[noot]

I'm impressed.
Tia!

Just curious, can I also attach a sheet here to make it easier to explain
and solve the problem?
(using Windows Live Mail, I see an "attach file" button?)

 

[L. Howard Kittle]

Don't send one to the newsgroup, it's generally frowned upon and, I'm not
sure the newsgroup even will even accept it.

Usually you can provide enough detail and someone will figure it out.
Details are important.

If you want, send me a sample workbook and explain in detail what you want
to happen and where etc.

(e-mail address removed)

Regards,
Howard