I am working on a project right now that I need to automatically
update based on a report that I run every month. Here is what the
report looks like right now.

SalesRep CompanyCode Number Cost
Spread Margin File Avg
AUTR 1 94 $27,023.67 $9,114.65
34.9% $96.96
AUTR 2 41 $10,114.17 $4,875.64
34.9% $118.92
AUTR 3 21 $3,136.23
$1,254.64 34.9% $59.74
AUTR 4 1 $1,750.00 $416.00
34.9% $416.00
AUTR 5 1 $1,550.00
$219.81 34.9% $219.81
AUTR 6 17 $2,865.36
$346.82 34.9% $20.40
AUTR 175 $46,439.43 $16,227.56 34.9%
$92.73
AYLE 1 15 $22,400.00
$5,165.69 25.3% $344.38
AYLE 2 16 $11,575.00
$2,601.70 25.3% $162.61
AYLE 3 15 $9,942.50
$2,045.75 25.3% $136.38
AYLE 4 2 $3,900.00
$1,810.00 25.3% $905.00
AYLE 5 6 $4,880.00
$1,686.73 25.3% $281.12
AYLE 6 96
$104,862.50 $26,509.50 25.3% $276.14
AYLE 150 $157,560.00
$39,819.37 25.3% $265.46

There are 7 colums and 7 rows associated to each salesperson. The
final row is the total for that salesperson for the entire month. What
I would like is to be able to link the worksheets together and return
each corresponding line of information.

The formula I am using now is
=VLOOKUP("autr",'[top carriers.xls]Sheet1'!$A$1:$K$434,{2},0)

This works fine to get the first line of information, but my problem
is that I can't get the last 6 rows of information for each
salesperson. Anyone have any ideas that could help me? I really need
it to be able to automatically update everytime the top carriers
worksheet is updated. Thanks for any ideas you can come up with.
Anything is better than what I have now.

If more than one row is needed, consider using a AutoFilter.
--
Gary''s Student - gsnu200731


" wrote:

I am working on a project right now that I need to automatically
update based on a report that I run every month. Here is what the
report looks like right now.

SalesRep CompanyCode Number Cost
Spread Margin File Avg
AUTR 1 94 $27,023.67 $9,114.65
34.9% $96.96
AUTR 2 41 $10,114.17 $4,875.64
34.9% $118.92
AUTR 3 21 $3,136.23
$1,254.64 34.9% $59.74
AUTR 4 1 $1,750.00 $416.00
34.9% $416.00
AUTR 5 1 $1,550.00
$219.81 34.9% $219.81
AUTR 6 17 $2,865.36
$346.82 34.9% $20.40
AUTR 175 $46,439.43 $16,227.56 34.9%
$92.73
AYLE 1 15 $22,400.00
$5,165.69 25.3% $344.38
AYLE 2 16 $11,575.00
$2,601.70 25.3% $162.61
AYLE 3 15 $9,942.50
$2,045.75 25.3% $136.38
AYLE 4 2 $3,900.00
$1,810.00 25.3% $905.00
AYLE 5 6 $4,880.00
$1,686.73 25.3% $281.12
AYLE 6 96
$104,862.50 $26,509.50 25.3% $276.14
AYLE 150 $157,560.00
$39,819.37 25.3% $265.46

There are 7 colums and 7 rows associated to each salesperson. The
final row is the total for that salesperson for the entire month. What
I would like is to be able to link the worksheets together and return
each corresponding line of information.

The formula I am using now is
=VLOOKUP("autr",'[top carriers.xls]Sheet1'!$A$1:$K$434,{2},0)

This works fine to get the first line of information, but my problem
is that I can't get the last 6 rows of information for each
salesperson. Anyone have any ideas that could help me? I really need
it to be able to automatically update everytime the top carriers
worksheet is updated. Thanks for any ideas you can come up with.
Anything is better than what I have now.

Are u trying to get the entire record (all 7 columns) for each salesman as
one value, or do you want each column to be separate? Basically, how are you
using the information in the first worksheet?

From your post I am assuming you don't want to have a vlookup() function in
49 different cells.

Cheers,
socratis

" wrote:

I am working on a project right now that I need to automatically
update based on a report that I run every month. Here is what the
report looks like right now.

SalesRep CompanyCode Number Cost
Spread Margin File Avg
AUTR 1 94 $27,023.67 $9,114.65
34.9% $96.96
AUTR 2 41 $10,114.17 $4,875.64
34.9% $118.92
AUTR 3 21 $3,136.23
$1,254.64 34.9% $59.74
AUTR 4 1 $1,750.00 $416.00
34.9% $416.00
AUTR 5 1 $1,550.00
$219.81 34.9% $219.81
AUTR 6 17 $2,865.36
$346.82 34.9% $20.40
AUTR 175 $46,439.43 $16,227.56 34.9%
$92.73
AYLE 1 15 $22,400.00
$5,165.69 25.3% $344.38
AYLE 2 16 $11,575.00
$2,601.70 25.3% $162.61
AYLE 3 15 $9,942.50
$2,045.75 25.3% $136.38
AYLE 4 2 $3,900.00
$1,810.00 25.3% $905.00
AYLE 5 6 $4,880.00
$1,686.73 25.3% $281.12
AYLE 6 96
$104,862.50 $26,509.50 25.3% $276.14
AYLE 150 $157,560.00
$39,819.37 25.3% $265.46

There are 7 colums and 7 rows associated to each salesperson. The
final row is the total for that salesperson for the entire month. What
I would like is to be able to link the worksheets together and return
each corresponding line of information.

The formula I am using now is
=VLOOKUP("autr",'[top carriers.xls]Sheet1'!$A$1:$K$434,{2},0)

This works fine to get the first line of information, but my problem
is that I can't get the last 6 rows of information for each
salesperson. Anyone have any ideas that could help me? I really need
it to be able to automatically update everytime the top carriers
worksheet is updated. Thanks for any ideas you can come up with.
Anything is better than what I have now.

On Jun 18, 7:38 pm, Socratis
wrote:
Are u trying to get the entire record (all 7 columns) for each salesman as
one value, or do you want each column to be separate? Basically, how are you
using the information in the first worksheet?

From your post I am assuming you don't want to have a vlookup() function in
49 different cells.

Cheers,
socratis


I don't mind having a vlookup in all the different cells. My problem
is just that I can't get the other rows to be found. I want to keep
all the recods seperate. Basically I want it to look exactly like that
but just on a different worksheet without me having to cut and paste
every month for over 50 salespeople. Like I said before, I can use
vlookup and get the first row, but when I try it again for the second
row all I get is the same information that's in the first row. Any
ideas?

See if this macro will work for u:

Public Sub GetSalesRecord()
Dim salesRange As Range
Dim salespersonRange As Range

Set salesRange = Workbooks("book2.xls").Names("SalesData").RefersTo Range
Set salespersonRange = salesRange.Find(what:=ActiveCell,
LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlColumns)
Set salespersonRange = salespersonRange.Resize(rowsize:=7 columnsize:=7)
salespersonRange.Copy Destination:=ActiveCell
End Sub

Define this macro in workbook1. Now, in each cell that u would like the 7x7
sales data for a given salesperson to be copied from [top carriers.xls],
enter their name and run the macro. It will copy their 7 rows over. For
example:

1) in A1 enter AUTR
A
1 AUTR

2) run the macro
3) Worksheet now looks like

A B ... G
1 AUTR ......
2 AUTR ......
....
7 AUTR .....

Note: There is a named range, "SalesData" in top [carriers.xls] that
includes all the sales records, including a row header. So, if you have 50
salesmen, this range should be 351x7.

HTH.
Cheers,
socratis

" wrote:

On Jun 18, 7:38 pm, Socratis
wrote:
Are u trying to get the entire record (all 7 columns) for each salesman as
one value, or do you want each column to be separate? Basically, how are you
using the information in the first worksheet?

From your post I am assuming you don't want to have a vlookup() function in
49 different cells.

Cheers,
socratis


I don't mind having a vlookup in all the different cells. My problem
is just that I can't get the other rows to be found. I want to keep
all the recods seperate. Basically I want it to look exactly like that
but just on a different worksheet without me having to cut and paste
every month for over 50 salespeople. Like I said before, I can use
vlookup and get the first row, but when I try it again for the second
row all I get is the same information that's in the first row. Any
ideas?

Ok here's the macro that I have. I probably have it all screwed up,
because it isn't working.

Public Sub GetSalesRecord()
Dim salesRange As Range
Dim salespersonRange As Range

Set salesRange = Workbooks("top
carriers.xls").Names("SalesRep").RefersToRange

Set salespersonRange = salesRange.Find(what:=ActiveCell,
LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlColumns)
Set salespersonRange = salespersonRange.Resize(rowsize:=7,
columnsize:=7)
salespersonRange.Copy Destination:=ActiveCell
End Sub

I'm not very good at Visual Basic so it's possible that I don't
understand what I was supposed to be doing. And where am I supposed to
put the range on where to find the data? I think that may be my
problem. But I think this would work if I can make this work. Thanks
for your help!

" wrote:

Ok here's the macro that I have. I probably have it all screwed up,
because it isn't working.

Public Sub GetSalesRecord()
Dim salesRange As Range
Dim salespersonRange As Range

Set salesRange = Workbooks("top
carriers.xls").Names("SalesRep").RefersToRange

Set salespersonRange = salesRange.Find(what:=ActiveCell,
LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlColumns)
Set salespersonRange = salespersonRange.Resize(rowsize:=7,
columnsize:=7)
salespersonRange.Copy Destination:=ActiveCell
End Sub

I'm not very good at Visual Basic so it's possible that I don't
understand what I was supposed to be doing. And where am I supposed to
put the range on where to find the data? I think that may be my
problem. But I think this would work if I can make this work. Thanks
for your help!


First create the macro:
- In the project worksheet, press alt+F11 to open the Visual Basic Editor.
- Select Insert | Module from the menu.
- Paste the code into the newly added module.
In the project worksheet:
- In the cell you want to pull the report data into, add the name of the
- salesperson, i.e. AUTR. Make sure this cell is selected before you
- run the macro (ActiveCell in the code). Also note, this will bring back
- a 7x7 area, so allow some space for that in the worksheet.
In the report worksheet:
- Create a named range ("SalesRep") to include your data. This includes the
- header row and should be 50 salespeople * 7 rows each + 1 header
- = 351 rows x 7 columns.
Run the macro:
- With a cell (containing the salesperson) selected in the project ws,
select
- Tools | Macro | Macro... from the menu and then select the macro name
- and click on run.

HTH

Cheers,
Socratis