Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formula help , please.
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formula help , please.
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formula help , please.
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formula help , please.
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formula help , please.
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formula help , please.
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formula help , please.
In article , GS writes
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... Hi Garry Nice to hear from you. Yes I agree. It's the ability to hit nails on heads with such piercing logic which is always most impressive. Best Wishes |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formula help , please.
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formula help , please.
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formula help , please.
Hi Colin,
Am Tue, 1 May 2012 11:37:58 +0200 schrieb Claus Busch: 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? I hope, I understood your problem. If you have xl2007 or later then try in row2 of sheet1: =IFERROR(IF(OR(INDEX(Sheet2!H:H,MATCH(A2,Sheet2!$B $1:$B$200,0))=3,INDEX(Sheet2!H:H,MATCH(A2,Sheet2!$ B$1:$B$200,0))INDEX(Sheet2!J:J,MATCH(A2,Sheet2!$B $1:$B$200,0))),INDEX(Sheet2!J:J,MATCH(A2,Sheet2!$B $1:$B$200,0)),E2),E2) If you have an earlier version try: =IF(COUNTIF(Sheet2!B:B,A2)=0,E2,IF(OR(INDEX(Sheet2 !H:H,MATCH(A2,Sheet2!$B$1:$B$200,0))=3,INDEX(Sheet 2!H:H,MATCH(A2,Sheet2!$B$1:$B$200,0))INDEX(Sheet2 !J:J,MATCH(A2,Sheet2!$B$1:$B$200,0))),INDEX(Sheet2 !J:J,MATCH(A2,Sheet2!$B$1:$B$200,0)),E2)) Or try this macro: Sub Checking() Dim LRow1 As Long Dim LRow2 As Long Dim rngC As Range Dim i As Long With Sheets("Sheet2") LRow1 = .Cells(.Rows.Count, 2).End(xlUp).Row LRow2 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row For Each rngC In .Range("B2:B" & LRow1) For i = 2 To LRow2 If rngC = Sheets("Sheet1").Cells(i, 1) Then If .Cells(rngC.Row, 8) = 3 Or .Cells(rngC.Row, 8) ..Cells(rngC.Row, 10) Then Sheets("Sheet1").Cells(i, 5) = .Cells(rngC.Row, 10) End If End If Next i Next rngC End With End Sub Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formula help , please.
Hi Claus OK a thousand thanks for your considerable help with this. It's doing the job fine. Best Wishes |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formula help , please.
Hi Colin,
Am Wed, 2 May 2012 18:03:55 +0100 schrieb Colin Hayes: It's doing the job fine. glad to help and thank you for feedback. What do you use? Formula or Macro? Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formula help , please.
In article , Claus Busch
writes Hi Colin, Am Wed, 2 May 2012 18:03:55 +0100 schrieb Colin Hayes: It's doing the job fine. glad to help and thank you for feedback. What do you use? Formula or Macro? Regards Claus Busch Hi Claus Well I'm experimenting with both. The macro causes a long pause while it processes. The formula works too. Both very good. I could probably build the formula into a macro too so it's all good. Best Wishes |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formula help , please.
Hi Colin,
Am Wed, 2 May 2012 22:22:00 +0100 schrieb Colin Hayes: Well I'm experimenting with both. The macro causes a long pause while it processes. The formula works too. Both very good. I could probably build the formula into a macro too so it's all good. this macro should be faster. There are no nested for each..next Please test it in a copy of your workbook: Sub Checking() Dim LRow1 As Long Dim LRow2 As Long Dim rngC As Range Dim myFind As Long With Sheets("Sheet2") LRow1 = .Cells(.Rows.Count, 2).End(xlUp).Row LRow2 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row For Each rngC In Sheets("Sheet1").Range("A2:A" & LRow2) If WorksheetFunction.CountIf(.Range("B1:B" & LRow2), rngC) 0 Then myFind = WorksheetFunction.Match(rngC, .Range("B1:B" & LRow1), 0) If .Cells(myFind, "H") = 3 Or _ .Cells(myFind, "H") .Cells(myFind, "J") Then rngC.Offset(0, 4) = .Cells(myFind, "J") End If End If Next rngC End With End Sub Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex IF formula | Excel Worksheet Functions | |||
complex formula | Excel Programming | |||
complex color fill conditions- if statements or complex formula? | Excel Discussion (Misc queries) | |||
need complex if formula (I think) | Excel Worksheet Functions | |||
Need Help with complex formula | Excel Worksheet Functions |