Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Question. Index/Match then Copy/Paste to Summary Sheet
I have a list of names and sales revenues associated with those names. I
have names in Column B of Sheet1 and Sales figures in Column C of Sheet1. I am hoping to be able to find a way of entering an identifier, such as an €śX€ť into Column D of Sheet1, then have Excel lookup the name, which is in Column B on Sheet2. I think Index/Match would work. Now, that the Name in Column B on Sheet1 is matched with the Name in Column B on Sheet2, find where the value changes (i.e., the name of the director changes) and copy this array plus one Column to the right (so Sheet2, Column B and Column C) because these are the sales reps that report to the director. Id like to take the result and copy/paste to a Summary Sheet. Finally, the sales revenue is in Column C of Sheet1. I would love to be able to copy this value to the Summary Sheet too. Pretty complicated, but Im sure it can be done. Ill be up late tonight, working on the code for this. Ill post back with some VBA, once I make a little progress on this. I am just posting now to see if anyone here has some generic code that may work in this situation. I know its a long shot, but Ive seem some AMAZING things here over the past couple of years. Finally, if I can figure this out myself, Ill post the results. Im not feeling very creative at this very moment though€¦ I would suspect, it would start like this: Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("D:D") Set t = Target If Intersect(r, t) Is Nothing Then Exit Sub If t.Value = "X" Then Application.EnableEvents = False 'ALL CODE HERE................................... Application.EnableEvents = True End If End Sub Thanks, Ryan--- -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Question. Index/Match then Copy/Paste to Summary Sheet
I tried this, mostly air-code:
Sub foo() Dim i As Integer LastRow = Range("B" & Rows.Count).End(xlUp).Row For i = 1 To LastRow If Worksheets("Sheet1").Cells(i, 2) = Worksheets("Sheet2").Cells(i, 2) Then Worksheets("Sheet2").Cells(i, 2).Copy Worksheets("SummarySheet").Cells(i, 2).Paste Else End If Next i End Sub I suppose the macro fires, but nothings actually happens. What am I doing wrong here? I would like to find the value in Sheet 1, Column B, that matches each value in Sheet2, Column B, and copy paste these matches, as well as two column to the right of each match, to a summary sheet. Thanks, Ryan--- -- RyGuy "ryguy7272" wrote: I have a list of names and sales revenues associated with those names. I have names in Column B of Sheet1 and Sales figures in Column C of Sheet1. I am hoping to be able to find a way of entering an identifier, such as an €śX€ť into Column D of Sheet1, then have Excel lookup the name, which is in Column B on Sheet2. I think Index/Match would work. Now, that the Name in Column B on Sheet1 is matched with the Name in Column B on Sheet2, find where the value changes (i.e., the name of the director changes) and copy this array plus one Column to the right (so Sheet2, Column B and Column C) because these are the sales reps that report to the director. Id like to take the result and copy/paste to a Summary Sheet. Finally, the sales revenue is in Column C of Sheet1. I would love to be able to copy this value to the Summary Sheet too. Pretty complicated, but Im sure it can be done. Ill be up late tonight, working on the code for this. Ill post back with some VBA, once I make a little progress on this. I am just posting now to see if anyone here has some generic code that may work in this situation. I know its a long shot, but Ive seem some AMAZING things here over the past couple of years. Finally, if I can figure this out myself, Ill post the results. Im not feeling very creative at this very moment though€¦ I would suspect, it would start like this: Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("D:D") Set t = Target If Intersect(r, t) Is Nothing Then Exit Sub If t.Value = "X" Then Application.EnableEvents = False 'ALL CODE HERE................................... Application.EnableEvents = True End If End Sub Thanks, Ryan--- -- RyGuy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Question. Index/Match then Copy/Paste to Summary Shee
Please disregard this post. I am now thinking that I didn't communicate my
thoughts, or intentions, well at all. Please disregard. I will soon repost, with a more clear, and more concise, description of the issue. Thanks, Ryan-- -- RyGuy "ryguy7272" wrote: I tried this, mostly air-code: Sub foo() Dim i As Integer LastRow = Range("B" & Rows.Count).End(xlUp).Row For i = 1 To LastRow If Worksheets("Sheet1").Cells(i, 2) = Worksheets("Sheet2").Cells(i, 2) Then Worksheets("Sheet2").Cells(i, 2).Copy Worksheets("SummarySheet").Cells(i, 2).Paste Else End If Next i End Sub I suppose the macro fires, but nothings actually happens. What am I doing wrong here? I would like to find the value in Sheet 1, Column B, that matches each value in Sheet2, Column B, and copy paste these matches, as well as two column to the right of each match, to a summary sheet. Thanks, Ryan--- -- RyGuy "ryguy7272" wrote: I have a list of names and sales revenues associated with those names. I have names in Column B of Sheet1 and Sales figures in Column C of Sheet1. I am hoping to be able to find a way of entering an identifier, such as an €śX€ť into Column D of Sheet1, then have Excel lookup the name, which is in Column B on Sheet2. I think Index/Match would work. Now, that the Name in Column B on Sheet1 is matched with the Name in Column B on Sheet2, find where the value changes (i.e., the name of the director changes) and copy this array plus one Column to the right (so Sheet2, Column B and Column C) because these are the sales reps that report to the director. Id like to take the result and copy/paste to a Summary Sheet. Finally, the sales revenue is in Column C of Sheet1. I would love to be able to copy this value to the Summary Sheet too. Pretty complicated, but Im sure it can be done. Ill be up late tonight, working on the code for this. Ill post back with some VBA, once I make a little progress on this. I am just posting now to see if anyone here has some generic code that may work in this situation. I know its a long shot, but Ive seem some AMAZING things here over the past couple of years. Finally, if I can figure this out myself, Ill post the results. Im not feeling very creative at this very moment though€¦ I would suspect, it would start like this: Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("D:D") Set t = Target If Intersect(r, t) Is Nothing Then Exit Sub If t.Value = "X" Then Application.EnableEvents = False 'ALL CODE HERE................................... Application.EnableEvents = True End If End Sub Thanks, Ryan--- -- RyGuy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Question. Index/Match then Copy/Paste to Summary Shee
Resolved!! When I sat down and REALLY thought about my needs for this project, I was able to rework some things and eventually come up with a solution. Everything is working now!! Thanks for the push!! http://www.microsoft.com/office/comm...sloc=en-us&p=1 Thanks goes out to JLGWhiz! Ryan-- -- RyGuy "ryguy7272" wrote: Please disregard this post. I am now thinking that I didn't communicate my thoughts, or intentions, well at all. Please disregard. I will soon repost, with a more clear, and more concise, description of the issue. Thanks, Ryan-- -- RyGuy "ryguy7272" wrote: I tried this, mostly air-code: Sub foo() Dim i As Integer LastRow = Range("B" & Rows.Count).End(xlUp).Row For i = 1 To LastRow If Worksheets("Sheet1").Cells(i, 2) = Worksheets("Sheet2").Cells(i, 2) Then Worksheets("Sheet2").Cells(i, 2).Copy Worksheets("SummarySheet").Cells(i, 2).Paste Else End If Next i End Sub I suppose the macro fires, but nothings actually happens. What am I doing wrong here? I would like to find the value in Sheet 1, Column B, that matches each value in Sheet2, Column B, and copy paste these matches, as well as two column to the right of each match, to a summary sheet. Thanks, Ryan--- -- RyGuy "ryguy7272" wrote: I have a list of names and sales revenues associated with those names. I have names in Column B of Sheet1 and Sales figures in Column C of Sheet1. I am hoping to be able to find a way of entering an identifier, such as an €śX€ť into Column D of Sheet1, then have Excel lookup the name, which is in Column B on Sheet2. I think Index/Match would work. Now, that the Name in Column B on Sheet1 is matched with the Name in Column B on Sheet2, find where the value changes (i.e., the name of the director changes) and copy this array plus one Column to the right (so Sheet2, Column B and Column C) because these are the sales reps that report to the director. Id like to take the result and copy/paste to a Summary Sheet. Finally, the sales revenue is in Column C of Sheet1. I would love to be able to copy this value to the Summary Sheet too. Pretty complicated, but Im sure it can be done. Ill be up late tonight, working on the code for this. Ill post back with some VBA, once I make a little progress on this. I am just posting now to see if anyone here has some generic code that may work in this situation. I know its a long shot, but Ive seem some AMAZING things here over the past couple of years. Finally, if I can figure this out myself, Ill post the results. Im not feeling very creative at this very moment though€¦ I would suspect, it would start like this: Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("D:D") Set t = Target If Intersect(r, t) Is Nothing Then Exit Sub If t.Value = "X" Then Application.EnableEvents = False 'ALL CODE HERE................................... Application.EnableEvents = True End If End Sub Thanks, Ryan--- -- RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy two summary ranges to master summary sheet | Excel Programming | |||
How to copy from each sheet and paste to a summary sheet | Excel Programming | |||
Countif and Index Match copy and paste | Excel Discussion (Misc queries) | |||
Complex Index Match Help (or at least complex to me) | Excel Discussion (Misc queries) | |||
Returning row # using match or index of repeated text in a complex table | Excel Worksheet Functions |