The best I have come up with so far does not start with filtering......

I copy the unfiltered data from Column R to Column AF.

Then I use Home/Find & Select/Replace to gradually remove the…]]>

The best I have come up with so far does not start with filtering......

I copy the unfiltered data from Column R to Column AF.

Then I use Home/Find & Select/Replace to gradually remove the names in Column AF that are NOT of interest.

Eventually, this leaves the name[s] of interest.

This triggers the correct results in Column AO which I can filter for "Yes".

Now I can see a presentable report in Columns K to T.

]]>Thank you for your suggestion. I did remove comma but how i can get rid of decimal point and i need comma because French Canadian use comma instead of decimal point.

And it is still not working that way like - 1 500 000,00

Thank you again for your…]]>

Thank you for your suggestion. I did remove comma but how i can get rid of decimal point and i need comma because French Canadian use comma instead of decimal point.

And it is still not working that way like - 1 500 000,00

Thank you again for your help. Please let me know how i can replace "." with ","

Many Regards,

Niky

]]>I need your assistance with a vba code. I don't know how to change it to do what I need unfortunately.

To make things easier, I will upload my excel file here.

Please check the code below:

(Code, 16 lines)

My excel file has at the moment 9 sheets but…]]>

I need your assistance with a vba code. I don't know how to change it to do what I need unfortunately.

To make things easier, I will upload my excel file here.

Please check the code below:

Code

- Sub Worksheet_Change(ByVal Target As Range)
- If Not Intersect(Target, Range("H14:H25")) Is Nothing Then
- Dim ws2 As Worksheet: Set ws2 = Sheet2
- Dim ws3 As Worksheet: Set ws3 = Sheet3
- x = Application.Match(Range("B" & Target.Row), ws2.Range("B4:B500"), 0)
- If IsNumeric(x) Then
- For i = 7 To Columns.Count
- If ws2.Cells(1, i).EntireColumn.Hidden = False Then
- ws2.Range("G" & x + 3).Offset(0, i - 7) = ws2.Range("G" & x + 3).Offset(0, i - 7).Value2 + CStr(Target.Value)
- GoTo MyEnd
- End If
- Next i
- End If
- End If
- MyEnd:
- End Sub

My excel file has at the moment 9 sheets but it will have more sheets similar to the sheets 6 - sheet 9.

What I want is to make the vba code that's added into Sheet 3, to show the result also in Sheet 6-sheet x, (where x is the last sheet I will add), based on 1 condition.

Example:

If WS3: B14 = 13396, and H14 = whatever,

Then show the result of the vba code in **WS2, G329** ( 329 being the matching row of the 13396 code inside WS2.)

But also, if I change the B14 with B14 = 12375, and H14 = whatever,

Then show the result of the vba code in **WS2, G98** ( 98 being the matching row of the 12375 code inside WS2).

What I said above is what the code does now. I need it to also do this:

If WS3: B14 = 13396, and H14 = whatever,** and G9=Sheet6 (It won't actually say sheet 6, but it will be a data validation list)**

Then show the result of the vba code in **WS2, G329** ( 329 being the matching row of the 13396 code inside WS2.)

**And also show the result of the vba code in WS6, F6 (6 being the matching row of the 13396 code inside WS6)**

If WS3: B14 = 13396, and H14 = whatever,** and G9=Sheet8**

Then show the result of the vba code in **WS2, G329** ( 329 being the matching row of the 13396 code inside WS2.)

**And also show the result of the vba code in WS8, F6 (6 being the matching row of the 13396 code inside WS8)**

**If G9 = empty, then the vba code should do just the first part where the result is shown only in WS2.**

I want to mention that the value of 13396 inside Sheet6 to Sheet x, won't be always at the same row.

1336 can be in Sheet 6 in row 6, but in Sheet 15 can be in row 25. The fact that both Sheet 6 and Sheet 8 has the code 1336 in B6 is a coincidence.

Basically, I want the code to show the results in **ws2 everytime**, but also to match the row in the other worksheets and based on the condition that G9=whatever, to show the result in the specified sheet too, at the correct row.

__I think that it is important to say, I don't need the hidden columns part for the other sheets. Just for WS2 as it is now, but if it's easier to let it there, then let it there.__

Any help with this issue will be much appreciated.

Thank you

This is what I need the code to do.

]]>Post back if you need further help.

Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.]]>

Post back if you need further help.

Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.

]]>Your problem could be solved without macros by simply converting to an Excel Table which can have a Total Row automatically. A Table is dynamic and so will expand as data is…]]>

Your problem could be solved without macros by simply converting to an Excel Table which can have a Total Row automatically. A Table is dynamic and so will expand as data is added and the Total row will move as well.

This code you wrote here does everything I need it to do. Thank you from the bottom of my heart. You really saved my day. If you didn't do this, I would have had to enter each line manually.

(Quote from Justin Doward)

]]>

This code you wrote here does everything I need it to do. Thank you from the bottom of my heart. You really saved my day. If you didn't do this, I would have had to enter each line manually. **Thank you again for this**

]]>Okay, I think we got there. It makes sense now.

Display MoreCode

- Sub Worksheet_Change(ByVal Target As Range)
- If Not Intersect(Target, Range("H14:H25")) Is Nothing Then
- Dim ws2 As Worksheet: Set ws2 = Sheet2
- Dim ws3 As Worksheet: Set ws3 = Sheet3
- x = Application.Match(Range("B" & Target.Row), ws2.Range("B4:B500"), 0)
- If IsNumeric(x) Then
- For i = 7 To Columns.Count
- If ws2.Cells(1, i).EntireColumn.Hidden = False Then
- ws2.Range("G" & x + 3).Offset(0, i - 7) = ws2.Range("G" & x + 3).Offset(0, i - 7).Value2 & CStr(Target.Value)
- GoTo MyEnd
- End If
- Next i
- End If
- End If
- MyEnd:
- End Sub

If this has solved it, can you mark it as the answer on stack exchange as well. Cheers Justin

Post back if you need further help.

Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.]]>

Post back if you need further help.

Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.

]]>I will look into why you are having to do it repeatedly.…]]>

I will look into why you are having to do it repeatedly.

]]>1] In "Sheet1" E4, formula copied right to F4 and all copied down :

=IFERROR(INDEX(A$4:A$24,AGGREGATE(15,6,ROW($A$1:$A$21)/((TEXT($A$3:$A$23,"0;;0;\0")="0")),ROW($A1))),"")

2] In "Sheet1" G4, array (confirm pressing with…]]>

1] In "Sheet1" E4, formula copied right to F4 and all copied down :

=IFERROR(INDEX(A$4:A$24,AGGREGATE(15,6,ROW($A$1:$A$21)/((TEXT($A$3:$A$23,"0;;0;\0")="0")),ROW($A1))),"")

2] In "Sheet1" G4, array (confirm pressing with Ctrl+Shift+Enter 3 keystrokes altogether) formula copied down :

=IF(F4="","",TEXTJOIN(", ",,IF(Table1_2[Column2]=F4,Table1_2[Column3],"")))

=SUM(IF(A:B=G2,IF(ROW(A:B)>=LARGE(IF(A:B=G2,ROW(A:B)),1),C:D)))

This here gives me the goals for in the last game. But is there anyway to calcuate the goals against in the last game?…]]>

=SUM(IF(A:B=G2,IF(ROW(A:B)>=LARGE(IF(A:B=G2,ROW(A:B)),1),C:D)))

This here gives me the goals for in the last game. But is there anyway to calcuate the goals against in the last game? Im having trouble to finding the opposite.

Thanks

]]>Post back if you need further help.

Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.]]>

Post back if you need further help.

Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.

]]>Attach an example workbook]]>

Attach an example workbook

]]>