Hello everyone,

If I does not use the right words in English, please forgive me, I'm used to
type in Dutch...

I have 3 worksheets to work with.
In the first worksheet, I have a cell with a date (today) and a cell with a
time (today's time). This time changes every minute.
I also have a range of 7 columns and about 12 rows. In this range are only
the cells of the 2nd row filled with values that does not change. All the
other cells are filled with a formula for getting the values out of an other
program.

In the second worksheet, I have a range of 14 columns and about 12 rows.
In this range, the cells of the first row will be filled with values by
someone. The other cells are empty.

What I like to realise is the following:

1) When someone opens the workbook, and fills the first row of worksheet 2,
that automaticly the value of this cells will be compared with the values of
the first row on worksheet 1. If the value matches, the value of the second
row on worksheet 1 (same column) need to be copied into the cell beneath the
matching value on worksheet 2. If there's no match, that cell remain empty.

2) If a cell on worksheet 1, in the first row is changed (this changes
automaticly), then that value must be compared with the values on worksheet 2
in row 1. If that value exists, the value of the cell beneath (on worksheet
1) must be copied into the cell beneath the matching value.
If all the cells on worksheet 2, 2nd row are filled with values, then the
values need to be copied to worksheet 3. Worksheet 3 is an exact copy of
worksheet 2.

I hope someone can help me solving this.
If I have to use VBA-code, please tell me where I have to put this code in
(in "Worksheet" or "this workbook") or do I have to make a module?

I hope I followed your logic... I think you can do this without using VBA.

In the solution below, the three worksheets are named "Sheet1", "Sheet2",
and "Sheet3"

On row 2 of Sheet 2, use the following formula:

=IF(A1=Sheet1!A1,Sheet1!A2,"")

On row 2 of Sheet3, use the following formula:

=IF(COUNTIF(Sheet2!$A$2:$E$2,"")=0,Sheet2!A2,"")

Fill across the range you are using on row 2 of both sheets.

Using this logic, Sheet3 will only have data in row 2 when all cells in row
1 of Sheet1 match cells in row 1 of Sheet2.

Regards...

p.s. Your typing in English is much better than mine would be in Dutch.



"Cheetahke" wrote:

Hello everyone,

If I does not use the right words in English, please forgive me, I'm used to
type in Dutch...

I have 3 worksheets to work with.
In the first worksheet, I have a cell with a date (today) and a cell with a
time (today's time). This time changes every minute.
I also have a range of 7 columns and about 12 rows. In this range are only
the cells of the 2nd row filled with values that does not change. All the
other cells are filled with a formula for getting the values out of an other
program.

In the second worksheet, I have a range of 14 columns and about 12 rows.
In this range, the cells of the first row will be filled with values by
someone. The other cells are empty.

What I like to realise is the following:

1) When someone opens the workbook, and fills the first row of worksheet 2,
that automaticly the value of this cells will be compared with the values of
the first row on worksheet 1. If the value matches, the value of the second
row on worksheet 1 (same column) need to be copied into the cell beneath the
matching value on worksheet 2. If there's no match, that cell remain empty.

2) If a cell on worksheet 1, in the first row is changed (this changes
automaticly), then that value must be compared with the values on worksheet 2
in row 1. If that value exists, the value of the cell beneath (on worksheet
1) must be copied into the cell beneath the matching value.
If all the cells on worksheet 2, 2nd row are filled with values, then the
values need to be copied to worksheet 3. Worksheet 3 is an exact copy of
worksheet 2.

I hope someone can help me solving this.
If I have to use VBA-code, please tell me where I have to put this code in
(in "Worksheet" or "this workbook") or do I have to make a module?

Sorry, ChristopherTri, this formula does not work, because when the values in
the cell "Sheet1!A1" changes to another value, I get the empty cell in Sheet
2. The values must remain there until all the cells of Sheet 2 are filled
with values. I start with an empty sheet 2.
I will try to explain it with an example.

On Sheet 1 I get information from another program. This program is called PI.


C D E F G
H I J
numbers 101 102 103 104 105 106 107
108
other info A206 A207 A208 A209 A210 A211 A212
A213

The values in the row "numbers" in every column changes frequently to other
numbers, let's say from 109 to 116 (and so on).
The values in the row "other info" don't change.

On Sheet 2, I start with empty cells.

B C D E F -- to O
numbers
other info

so, when I type in the row "numbers", column B a number, let's say 104, then
Excel have to look on sheet 1 if that number exists in the row "numbers". If
the value is found, in the row "other info", column B, "A209" have to appear.
When on sheet 1 the values of the row "numbers" changes, the value of the
cells on sheet 2 must remain there, they may not disappear anymore.
This must also be done when I type in column C, row "numbers" a following
value... and so on until the range "B" to "O" is filled with values.

"ChristopherTri" wrote:

I hope I followed your logic... I think you can do this without using VBA.

In the solution below, the three worksheets are named "Sheet1", "Sheet2",
and "Sheet3"

On row 2 of Sheet 2, use the following formula:

=IF(A1=Sheet1!A1,Sheet1!A2,"")

On row 2 of Sheet3, use the following formula:

=IF(COUNTIF(Sheet2!$A$2:$E$2,"")=0,Sheet2!A2,"")

Fill across the range you are using on row 2 of both sheets.

Using this logic, Sheet3 will only have data in row 2 when all cells in row
1 of Sheet1 match cells in row 1 of Sheet2.

Regards...

p.s. Your typing in English is much better than mine would be in Dutch.



"Cheetahke" wrote:

Hello everyone,

If I does not use the right words in English, please forgive me, I'm used to
type in Dutch...

I have 3 worksheets to work with.
In the first worksheet, I have a cell with a date (today) and a cell with a
time (today's time). This time changes every minute.
I also have a range of 7 columns and about 12 rows. In this range are only
the cells of the 2nd row filled with values that does not change. All the
other cells are filled with a formula for getting the values out of an other
program.

In the second worksheet, I have a range of 14 columns and about 12 rows.
In this range, the cells of the first row will be filled with values by
someone. The other cells are empty.

What I like to realise is the following:

1) When someone opens the workbook, and fills the first row of worksheet 2,
that automaticly the value of this cells will be compared with the values of
the first row on worksheet 1. If the value matches, the value of the second
row on worksheet 1 (same column) need to be copied into the cell beneath the
matching value on worksheet 2. If there's no match, that cell remain empty.

2) If a cell on worksheet 1, in the first row is changed (this changes
automaticly), then that value must be compared with the values on worksheet 2
in row 1. If that value exists, the value of the cell beneath (on worksheet
1) must be copied into the cell beneath the matching value.
If all the cells on worksheet 2, 2nd row are filled with values, then the
values need to be copied to worksheet 3. Worksheet 3 is an exact copy of
worksheet 2.

I hope someone can help me solving this.
If I have to use VBA-code, please tell me where I have to put this code in
(in "Worksheet" or "this workbook") or do I have to make a module?

Hi Chris,
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Find1 As Object
If Target.Count 1 Or Target.Row 1 Then Exit Sub
If Not IsEmpty(Target.Value) Then
Set Find1 = Sheets(1).Rows(1). _
Find(Target.Value, LookIn:=xlValues)
If Not Find1 Is Nothing Then
Cells(Target.Row + 1, Target.Column).Value = _
Find1.Offset(1, 0)
Else
Cells(Target.Row + 1, Target.Column).Value = ""
End If
Else
Cells(Target.Row + 1, Target.Column).Value = ""
End If
Cells.Copy Sheets(3).[a1] 'becarefull with this,
' you have to understand before use this
' because may replase your values in sheet3 ...
End Sub

--

Regards,

Halim


"ChristopherTri" wrote:

I hope I followed your logic... I think you can do this without using VBA.

In the solution below, the three worksheets are named "Sheet1", "Sheet2",
and "Sheet3"

On row 2 of Sheet 2, use the following formula:

=IF(A1=Sheet1!A1,Sheet1!A2,"")

On row 2 of Sheet3, use the following formula:

=IF(COUNTIF(Sheet2!$A$2:$E$2,"")=0,Sheet2!A2,"")

Fill across the range you are using on row 2 of both sheets.

Using this logic, Sheet3 will only have data in row 2 when all cells in row
1 of Sheet1 match cells in row 1 of Sheet2.

Regards...

p.s. Your typing in English is much better than mine would be in Dutch.



"Cheetahke" wrote:

Hello everyone,

If I does not use the right words in English, please forgive me, I'm used to
type in Dutch...

I have 3 worksheets to work with.
In the first worksheet, I have a cell with a date (today) and a cell with a
time (today's time). This time changes every minute.
I also have a range of 7 columns and about 12 rows. In this range are only
the cells of the 2nd row filled with values that does not change. All the
other cells are filled with a formula for getting the values out of an other
program.

In the second worksheet, I have a range of 14 columns and about 12 rows.
In this range, the cells of the first row will be filled with values by
someone. The other cells are empty.

What I like to realise is the following:

1) When someone opens the workbook, and fills the first row of worksheet 2,
that automaticly the value of this cells will be compared with the values of
the first row on worksheet 1. If the value matches, the value of the second
row on worksheet 1 (same column) need to be copied into the cell beneath the
matching value on worksheet 2. If there's no match, that cell remain empty.

2) If a cell on worksheet 1, in the first row is changed (this changes
automaticly), then that value must be compared with the values on worksheet 2
in row 1. If that value exists, the value of the cell beneath (on worksheet
1) must be copied into the cell beneath the matching value.
If all the cells on worksheet 2, 2nd row are filled with values, then the
values need to be copied to worksheet 3. Worksheet 3 is an exact copy of
worksheet 2.

I hope someone can help me solving this.
If I have to use VBA-code, please tell me where I have to put this code in
(in "Worksheet" or "this workbook") or do I have to make a module?

Hello Halim,

Chris wasn't asking for this code, but I did...
But... I've some troubles with the code... it does not work. When I put a
value in sheet 2, Excel don't put the matching value from sheet 1 in de cell
beneath.


"Halim" wrote:

Hi Chris,
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Find1 As Object
If Target.Count 1 Or Target.Row 1 Then Exit Sub
If Not IsEmpty(Target.Value) Then
Set Find1 = Sheets(1).Rows(1). _
Find(Target.Value, LookIn:=xlValues)
If Not Find1 Is Nothing Then
Cells(Target.Row + 1, Target.Column).Value = _
Find1.Offset(1, 0)
Else
Cells(Target.Row + 1, Target.Column).Value = ""
End If
Else
Cells(Target.Row + 1, Target.Column).Value = ""
End If
Cells.Copy Sheets(3).[a1] 'becarefull with this,
' you have to understand before use this
' because may replase your values in sheet3 ...
End Sub

--

Regards,

Halim


"ChristopherTri" wrote:

I hope I followed your logic... I think you can do this without using VBA.

In the solution below, the three worksheets are named "Sheet1", "Sheet2",
and "Sheet3"

On row 2 of Sheet 2, use the following formula:

=IF(A1=Sheet1!A1,Sheet1!A2,"")

On row 2 of Sheet3, use the following formula:

=IF(COUNTIF(Sheet2!$A$2:$E$2,"")=0,Sheet2!A2,"")

Fill across the range you are using on row 2 of both sheets.

Using this logic, Sheet3 will only have data in row 2 when all cells in row
1 of Sheet1 match cells in row 1 of Sheet2.

Regards...

p.s. Your typing in English is much better than mine would be in Dutch.



"Cheetahke" wrote:

Hello everyone,

If I does not use the right words in English, please forgive me, I'm used to
type in Dutch...

I have 3 worksheets to work with.
In the first worksheet, I have a cell with a date (today) and a cell with a
time (today's time). This time changes every minute.
I also have a range of 7 columns and about 12 rows. In this range are only
the cells of the 2nd row filled with values that does not change. All the
other cells are filled with a formula for getting the values out of an other
program.

In the second worksheet, I have a range of 14 columns and about 12 rows.
In this range, the cells of the first row will be filled with values by
someone. The other cells are empty.

What I like to realise is the following:

1) When someone opens the workbook, and fills the first row of worksheet 2,
that automaticly the value of this cells will be compared with the values of
the first row on worksheet 1. If the value matches, the value of the second
row on worksheet 1 (same column) need to be copied into the cell beneath the
matching value on worksheet 2. If there's no match, that cell remain empty.

2) If a cell on worksheet 1, in the first row is changed (this changes
automaticly), then that value must be compared with the values on worksheet 2
in row 1. If that value exists, the value of the cell beneath (on worksheet
1) must be copied into the cell beneath the matching value.
If all the cells on worksheet 2, 2nd row are filled with values, then the
values need to be copied to worksheet 3. Worksheet 3 is an exact copy of
worksheet 2.

I hope someone can help me solving this.
If I have to use VBA-code, please tell me where I have to put this code in
(in "Worksheet" or "this workbook") or do I have to make a module?