vLookup - how to pull data from a sheet to other sheets ...

[angelandgreg]

ok, i have an excel sheet where the first sheet is a data link to pull several columns as a data dump.

It has all the reps info on it.

What I need to do is on the next sheet have it look up from the data dump sheet the specific rep and pull all the columns of data into this next sheet and so on to other sheets for each change in rep.

it would be great if each sheet updated the data from the data dump sheet after it is refreshed.

it would also be great if each sheet (which is per rep) can lookup for that specific rep each time.

I hope this made sense??


Data Dump example:
REP Customer YTD Sales PTD Sales PVR Sales
tony 123 5000 500 20000
tony abc 400 0 300
bob 345 4000 400 10000
bob xyz 3000 200 0

next sheet: (which would be the 2nd sheet as the data dump is the first sheet in this worksheet)
REP Customer YTD Sales PTD Sales PVR Sales
tony 123 5000 500 20000
tony abc 400 0 300


3rd sheet:
REP Customer YTD Sales PTD Sales PVR Sales
bob 345 4000 400 10000
bob xyz 3000 200 0

 

[PHV]

Why duplicating all your data in so many sheets ?
Why not simply play with an AutoFilter ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[angelandgreg]

The sales dept manager wants each rep's info in their own separate sheet.

if it could be that easy to convince them to do just that

 

[angelandgreg]

basically, because they can't / won't do it, I have been doing the filter, copying then pasting the separate rep's info into their own sheet.

so i'm looking for a more automated way to do this so neither I or they need to do anything but open the sheet, refresh and view the data.

you know how management goes

 

[cresbydotcom]

record macro
then do what you would do manually (see below)
then look at the macro (VBA editor) for clues on how it is done. - it will usually be Macro X in a "module"

I would manually select "Tony's worksheet" select all used columns, sort on the name column (warning about headers)select name col, then "Find" the name in the name column and use "Selection.Row" & "Selection.Column" as the start point. add VBA to Inspect, in a loop, each name cell downwards for being the same as the chosen name until different and subtract 1 from the last row number.

after that Select "tony" rows. and copy, select rep's worksheet and paste in given place. The recorded macro (stop the macro!) will usually work without modification.

create a button to run "Tony" on tony's page (start point for tony) and similar on other rep's pages etc.

I often include a column of formulae in the master to look for cells that differ from the above cell (eg name in Z1) or are equal to a name in a cell I can put a name (in VBA) and

eg in COL Y "=IF($A2=$Z$1,row(),0)

then MAX(Y:Y) and MIN(Y:Y) in say Z2 & Z3 will yield the first and last rows of the "name" once the columns are sorted.

Hope this helps.

 

[cresbydotcom]

Oh - yeah. the reason I do it by cut and paste is so that any finger trouble tha crashes formulae is minimised. The formulae can be re-written with each run if necessary to remove this. The macro does not have to be in the same spreadsheet if management freak-out when seeing the "Macro" warning on opening the spreadsheet.Aerospace customers react the same, only moreso.

This is a little harder to engineer - "ActiveSheet" usually sorts most of this, but it is a pain.

 

[angelandgreg]

Humh, i've never worked with a marco before. I will have to see if I can do this and how far I get and if it works for me.

thanks for the tip anything more for someone who has never worked with macros and recording them in Excel, i can definitely use it.

 

[SkipVought]

This can be done quite easly without any code by using Data/Get External Data as a parameter query with the REP as the parameter.

Then just copy the sheet and change the rep.

Using MS Query to get data from Excel faq68-5829



Skip,

_[red][/red]

 

[angelandgreg]

Hi,
Ok, i have the data dump with the external link to my MS Access db.

The query has the info needed for all the reps.

i wanted to avoid creating a query per rep.

i just wanted to dump the data to Excel and do the vLookup on consecutive sheets per rep from the data dump sheet.

 

[SkipVought]

"i wanted to avoid creating a query per rep."

WHY? It's a heck of alot easier than creating LOOKUPs.

Actually, the best way is ONE query on ONE sheet with AutoFilter or the like. But you have this management issue.


Skip,

_[red][/red]

 

[angelandgreg]

yes, any other suggestions?

or some macro that can say copy the data into a new sheet each time the Repname changes from the data dump sheet? something like that?

 

[SkipVought]

Try turning on your macro recorder and performing what you want.

Post back with your recorded code.

Skip,

_[red][/red]

 

[angelandgreg]

well, the record macro will only work IF the rep groups do not change month to month.

 

[angelandgreg]

Sub Poplulate()
'
' Poplulate Macro
' Macro recorded 2/5/2007 by aellis
'

'
Rows("2:7").Select
Selection.Copy
Sheets("3-D").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
Sheets("Data Dump").Select
Rows("8:85").Select
Range("A85").Activate
ActiveWindow.SmallScroll Down:=-1
Selection.Copy
Sheets("CLO").Select
ActiveWindow.LargeScroll Down:=2
Rows("2:79").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveWindow.LargeScroll Down:=-2
Range("A2").Select
Sheets("Data Dump").Select
Selection.Copy
Sheets("CLO").Select
ActiveSheet.Paste
ActiveWindow.LargeScroll Down:=2
Application.CutCopyMode = False
ActiveWorkbook.Save
Sheets("Data Dump").Select
ActiveWindow.LargeScroll Down:=1
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 72
Rows("86:135").Select
Range("A135").Activate
Selection.Copy
Sheets("COY").Select
ActiveWindow.LargeScroll Down:=1
Rows("2:51").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveWindow.LargeScroll Down:=-1
Range("A2").Select
Sheets("Data Dump").Select
Selection.Copy
Sheets("COY").Select
ActiveSheet.Paste
ActiveWindow.LargeScroll Down:=1
Range("A39").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Sheets("Data Dump").Select
ActiveWindow.LargeScroll Down:=0
End Sub

 

[angelandgreg]

Rep MTD LYMTD YTD LYTD Total LY
3-D COMPANY Total $1,964.00 $0.00 $1,964.00 $0.00 $529.00
BUTLER SALES & MARKETING Total $271.50 $272.00 $271.50 $272.00 $1,883.50
CLOUGHESY & CO Total $96,256.81 $1,749.50 $96,256.81 $1,749.50 $99,043.81
COYNES & COMPANY Total $50,669.21 $350.40 $50,669.21 $350.40 $93,282.57
EMPLOYEE SALES Total $7.00 $0.00 $7.00 $0.00 $70.75
EXPECTATIONS Total $80,448.83 $6,021.72 $80,448.83 $6,021.72 $55,111.81
HAEFLING GROUP Total $31,744.88 $343.81 $31,744.88 $343.81 $13,465.38
HISKEY & COMPANY Total $19,923.21 $2,281.59 $19,923.21 $2,281.59 $42,170.80
HOUSE Total $7,063.49 $5,049.00 $7,063.49 $5,049.00 $141,965.51
INTEGRITEAM Total $105,966.19 $9,554.75 $105,966.19 $9,554.75 $153,103.67
INTERNATIONAL Total $85,454.10 $0.00 $85,454.10 $0.00 $1,082,667.27
JEHNZEN & ASSOCIATES Total $15,459.66 $0.00 $15,459.66 $0.00 $14,215.64
JUST THE BEST GIFTWARE Total $95,648.44 $21,348.84 $95,648.44 $21,348.84 $257,095.09
KING JOHN & ASSOC Total $22,355.13 $20.00 $22,355.13 $20.00 $20,444.02
LAWRENCE MARKETING Total $67,129.60 $334.00 $67,129.60 $334.00 $22,019.32
LELANI ALVES Total $5,249.00 $0.00 $5,249.00 $0.00 $7,832.50
MC CABE & ASSOCIATES Total $62,455.04 $2,916.71 $62,455.04 $2,916.71 $48,105.43
ONE COAST SOUTHWEST GIFT Total $288.50 $0.00 $288.50 $0.00 $0.00
PRAY & ASSOCIATES Total $34,405.02 $0.00 $34,405.02 $0.00 $16,432.47
REP SAMPLES Total $2,500.25 $24.00 $2,500.25 $24.00 $2,862.25
RICHARD S DOLAN INC Total $53,386.69 $0.00 $53,386.69 $0.00 $7,332.99
SOUTHERN LINK Total $246,152.86 $3,396.85 $246,152.86 $3,396.85 $125,648.92
STACK & COMPANY Total $39,185.66 $41.40 $39,185.66 $41.40 $24,523.95
STERNBERG & COMPANY Total $118,999.28 $145.50 $118,999.28 $145.50 $218,585.74
WILLIAM MOORE ASSOCIATES Total $18,346.03 $0.00 $18,346.03 $0.00 $4,939.62


Another rep group may show in a different month. Like the Butler was not a worksheet until this month's refresh of the data dump sheet.

 

[angelandgreg]

So I tried the VLOOKUP

=VLOOKUP("WILLIAM MOORE ASSOCIATES", A2:B22, 2, FALSE)

but for some reason when I get to:
=VLOOKUP("WILLIAM MOORE ASSOCIATES", A222, 4, FALSE)
it's returning 0 instead of the actual data in this field.

 

[SkipVought]

maybe it should be
[tt]
=VLOOKUP("WILLIAM MOORE ASSOCIATES Total", A222, 4, FALSE)
[/tt]


Skip,

_[red][/red]

 

[ck1999]

have you tried advanced filter

I use a spread sheet with a defined range Criteria="Open work orders a1:a2"

then in a module in vba i have


Sub Autpen()
'
' Autpen Macro
' List Open Work Orders
'

'
Worksheets("Open Work Orders").Activate
Rows("8:63").Select
Selection.ClearContents
Range("A8").Select
Sheets("Work Order Log").Range("A14:L825").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A8:L8"), _
Unique:=False
Selection.Sort Key1:=Range("B9"), Order1:=xlAscending, Key2:=Range("A9") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom



End Sub


This copies all work orders from sheet "work order log" and places them on "open work ordes"

Hope this may be of some help

ck1999

 

[angelandgreg]

still returning 0

NOTE: oddly enough the next column is returning the correct value but the one following it is also returning a 0 when it should be the address???

 

[ck1999]

make a worksheet labeled salesrep1

then in a1 enter "REP" (without quotes) since this is your column heading then in a2 enter "Tony"

then create this macro and run the code. This code will automatically run each time the workbook is opened

Sub Autpen()
'
' Autpen Macro
' List Open Work Orders
'

'
Worksheets("salesrep1").Activate
Rows("8:63").Select
Selection.ClearContents
Range("A8").Select
Sheets("data dump").Range("A14:L825").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A8:L8"), _
Unique:=False
Selection.Sort Key1:=Range("B9"), Order1:=xlAscending, Key2:=Range("A9") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom



End Sub

This is using columns a - L. Which you can change depending on your spreadsheet.

From what I understand this is what you are asking for. You can then make a sheet for each salesrep and then just change the criteria in cell a2.