Hi all


I'm wrestling with some complex formula. I'm hoping someone can assist
with a formula or macro to satisfy something I need to do in Excel.

Essentially I need to compare the content of two cells in separate
sheets. Where they are the same and certain other simple criteria are
met , then the content of the cells should be copied from one sheet to
the other.

This is the text to the formula / macro:

IF A2 in sheet 1 is the same as B2 in sheet 2

AND H2 in sheet 2 is equal to 3 or higher than J2 in sheet 2 ,

THEN make E2 in sheet 1 the same as J2 in sheet 2.

OTHERWISE leave E2 in sheet 1 as it is.


Grateful for any assistance with this complicated scenario.

Phew.

Hi Colin,

Am Mon, 30 Apr 2012 19:50:05 +0100 schrieb Colin Hayes:

IF A2 in sheet 1 is the same as B2 in sheet 2

AND H2 in sheet 2 is equal to 3 or higher than J2 in sheet 2 ,

THEN make E2 in sheet 1 the same as J2 in sheet 2.

OTHERWISE leave E2 in sheet 1 as it is.

if E2 is empty, you can use:
=IF(AND(OR(Sheet2!H2=3,Sheet2!H2Sheet2!J2),A2=She et2!B2),Sheet2!J2,"")
If E2 is not empty, you need a macro.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

In article , Claus Busch
writes
Hi Colin,

Am Mon, 30 Apr 2012 19:50:05 +0100 schrieb Colin Hayes:

IF A2 in sheet 1 is the same as B2 in sheet 2

AND H2 in sheet 2 is equal to 3 or higher than J2 in sheet 2 ,

THEN make E2 in sheet 1 the same as J2 in sheet 2.

OTHERWISE leave E2 in sheet 1 as it is.

if E2 is empty, you can use:
=IF(AND(OR(Sheet2!H2=3,Sheet2!H2Sheet2!J2),A2=Sh eet2!B2),Sheet2!J2,"")
If E2 is not empty, you need a macro.


Regards
Claus Busch


Hi Claus

Thanks for getting back so helpfully.

E2 on sheet one is not empty and does have content.

Could you help construct a macro?

Many thanks for your time and expertise.

Hi Colin,

Am Mon, 30 Apr 2012 20:44:21 +0100 schrieb Colin Hayes:

E2 on sheet one is not empty and does have content.

copy the code into the code modul of sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address < "$A$2" Then Exit Sub

With Sheets("Sheet2")
If Target = .[B2] And (.[H2] = 3 Or .[H2] .[J2]) Then
[E2] = .[J2]
End If
End With
End Sub

If you change A2 in Sheet1 and the conditions are true, the code
changes E2.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

In article , Claus Busch
writes
Hi Colin,

Am Mon, 30 Apr 2012 20:44:21 +0100 schrieb Colin Hayes:

E2 on sheet one is not empty and does have content.

copy the code into the code modul of sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address < "$A$2" Then Exit Sub

With Sheets("Sheet2")
If Target = .[B2] And (.[H2] = 3 Or .[H2] .[J2]) Then
[E2] = .[J2]
End If
End With
End Sub

If you change A2 in Sheet1 and the conditions are true, the code
changes E2.


Regards
Claus Busch


Hi Claus

OK That's excellent - thank you very much indeed. It does the job
exactly for the target line.

I'll need to try to adapt it so I can use as a formula in a more
traditional macro that I can run though.

This is because it needs to apply itself to all the rows in the sheet ,
one by one. Also the number in B2 will need to match against column A.
This is because it may be in future checks , the order may vary.
(Sorry!).

Perhaps helper columns for column E and some copying and some
pasting-back could be employed.

So if a match for B2 is found in column A then the relevant changes
would be made in that row. The macro would then check the number in B3
against all in column A and so on to the bottom of the sheet. Mind
Boggling!

Hi Colin,
It's been awhile since we exchanged. Just want to let you know that
Claus has a knack for making formulas that simplify working with
"mind-boggling" issues! I enjoy working through his solutions because
they're always a good learning exercise!

Best wishes...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Colin,

Am Mon, 30 Apr 2012 23:04:19 +0100 schrieb Colin Hayes:


I'll need to try to adapt it so I can use as a formula in a more
traditional macro that I can run though.

I thought, you would change E while you enter values in A, sorry.
Try the code below in a standard module:

Sub Checking()
Dim LRow As Long
Dim i As Long

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 2 To LRow
If .Cells(i, 1) = Sheets("Sheet2").Cells(i, 2) And _
(Sheets("sheet2").Cells(i, 8) = 3 Or Sheets("sheet2") _
.Cells(i, 8) = Sheets("sheet2").Cells(i, 10)) Then
.Cells(i, 5) = Sheets("Sheet2").Cells(i, 10)
End If
Next
End With
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Colin,

Am Mon, 30 Apr 2012 23:04:19 +0100 schrieb Colin Hayes:

So if a match for B2 is found in column A then the relevant changes
would be made in that row. The macro would then check the number in B3
against all in column A and so on to the bottom of the sheet. Mind
Boggling!

don't use the macro. First I have some questions.
If a match for B2 is found, i.e. in A7, is now to check whether H7=3 or
H7J7 and insert J7 in E7 if conditions are true?


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2