Find in wks1 and then copy to wks 3 !!Help

abshaw

Board Regular
Joined
Feb 12, 2004
Messages
53
:cry:
It might be a very simple question, but i am totally stuck with this problem. What i have is 3 worksheets in excel

sheet1
this is the data sheet with fields
a1 customer name
b1 monthly premium
c1 agent number.........and so on.

sheet 2
a1 agent number
b1 agent name
c1 agent address.........and so on.

Sheet 3

what i want to do here is put an agent number in A1 let us assume "60"
then a macro runs through the colomn c of sheet 1, and if a customer exists has an agent number= 60, then starting at row 5 in sheet 3 starts to copy the customer name and his monthly premium one by one from sheet 1.
for instance if there are total 3 customer that have agent 60 in sheet 1, at row 14, 27,and 59 respectively. Then in sheet 3 all i want is 3 rows with customer that had that number 60 in their c coloum (i-e rows 14,27and 59).

any thing or clue will help, i am just a dumb node, please help
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hi:

Not to change your direction, but have you considered a pivot table approach or a simple filtering technique?

these might do the job for you.

plettieri
 

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hi:

Maybe something like this would help...
Book1
BCDEFGHIJ
2Data
3customer namemonthly premiumagent numberagent nameagent address
4Mary$ 123.0060Smith111 Main St
5Bill$ 22.0059Jones23 Pearl Dr
6Ted$ 345.0060Lopez34 Hammer Dr
7Barbara$ 566.0065Huiw77 Lake Stagent number60
8Paul$ 432.0060Wong65 Park Ave
9Mike$ 11.0059Rite111 Main StSum of monthly premium
10customer nameTotal
11Mary123
12Paul432
13Ted345
14Grand Total900
15
16
Sheet1



hope this helps
plettieri
 

Mat

Well-known Member
Joined
Sep 17, 2003
Messages
509
Hi, what plettieri suggest might be a good alternative at what you asked. But in case you want exactly what you asked for, then assuming your sheet3 is displayed like this:
Classeur1
ABCD
1AgentCustomers
225Roger
3John
4Bob
5
6
7
Sheet3



Then add this code in the Sheet3 module, so each time you change the value in A2, then the customers names will change in column B.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
Dim i As Integer

Range([B2], [B65536].End(xlUp).Offset(1, 0)).ClearContents

i = 1
Do Until IsEmpty(Sheets("Sheet1").Cells(i, 3)) = True
    If Sheets("Sheet1").Cells(i, 3).Value = [A2].Value Then
        [B65536].End(xlUp).Offset(1, 0).Value = Sheets("Sheet1").Cells(i, 1).Value
    End If
    i = i + 1
Loop
End If

End Sub

maybe you'll need to change the "Sheet1" part of the code if Sheet1 isn't the real name of your first sheet...

Hope this helps,
 

abshaw

Board Regular
Joined
Feb 12, 2004
Messages
53

ADVERTISEMENT

plettieri thanks for your reply

its just that i am not able to copy any thing from this post, sorry for the trouble, but can you guide be a bit ahead, thanks.

Mat,
I copied your formula into my worksheet module, but it doesnt generate the desired result, plus can you please make it account for the money as well

thankyou
 

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hi abshar:

If I read your reply correctly, giving you some info on the pivot table approach is needed.....If you are not familar with the Wizard, it is a process by which Excel walks you through the steps of creating the pivot table...similar to what I posted in my previous post.

Here is some of the verbage help found on the help menu...

A PivotTable report is an interactive table that quickly combines and compares large amounts of data. You can rotate its rows and columns to see different summaries of the source data, and you can display the details for areas of interest.

When should I use a PivotTable report?

Use a PivotTable report when you want to analyze related totals, especially when you have a long list of figures to sum and you want to compare several facts about each figure. In the report illustrated above, you can easily see how the third-quarter golf sales in cell F3 stack up against sales for another sport or quarter, or the total sales. Because a PivotTable report is interactive, you can change the view of the data to see more details or calculate different summaries, such as counts or averages.

How does it organize my data?

In a PivotTable report, each column or field in your source data becomes a PivotTable field that summarizes multiple rows of information. In the example above, the Sport column becomes the Sport field, and each record for Golf is summarized in a single Golf item.

A data field, such as Sum of Sales, provides the values to be summarized. Cell F3 in the report above contains the sum of the Sales value from every row in the source data for which the Sport column contains Golf and the Quarter column contains Qtr3.

How do I create a PivotTable report?

To create a PivotTable report, you run the PivotTable and PivotChart Wizard. In the wizard, you select the source data you want from your worksheet list or external database. The wizard then provides you with a worksheet area for the report and a list of the available fields. As you drag the fields from the list window to the outlined areas, Microsoft Excel summarizes and calculates the report for you automatically.
If you're using an Office Data Connection to retrieve external data for your report, you can return the data directly to a PivotTable report, without running the PivotTable and PivotChart Wizard. Office Data Connections are the recommended method of retrieving external data for your reports when you don't need to combine data from more than one table in the external database or filter the data to select specific records before creating the report, and for retrieving data from OLAP databases.

After you create a PivotTable report, you can customize it to focus on the information you want: change the layout, change the format, or drill down to display more detailed data.


I appogogize if I over simplified or misunderstood your response.


PS It also might be helpful in organizing your data First.

plettieri
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,095
Messages
5,768,053
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top