Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
One sheet to another..
How can I create a macro which will lookup values from one sheet in
another, and copy corresponding values? Example: Sheet 1: ColA ColB Dogs Cats Pigs Sheet 2 ColA ColB Dogs 125 Gerbils 392 Cats 92 Pigs 423 Horses 32 I want it to look up if Sheet2 has 'Dogs' in column A, then to copy the value in of Dogs in B2 (125)into the corresponding column in Sheet1 columnB where Dogs is listed. It would ignore gerbils and horses, but put the values from Sheet2 in Sheet1 column B accordingly. Thank you for your help in advance!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
One sheet to another..
You shouldn't need a macro for that: Just use Vlookup() function. In Sheet1, B2 enter: =Vlookup(A2,Sheet2!A:B,2,0) then copy the formula down column the column. -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=558620 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
One sheet to another..
in column 2 of sheet1 (assume cell B1)
=if(A1="","",vlookup(A1,Sheet2!$A:$B,2,0)) then drag fill down. if you want code Sub DoLookups() Dim rng as Range With Worksheets("Sheet1") set rng = .Range(.Cells(1,1),.Cells(1,1).End(xldown)) End with rng.offset(0,1).formula = "=if(A1="""","""",vlookup(A1,Sheet2!$A:$B,2,0) )" rng.offset(0,1).Formula = rng.offset(0,1).Value End Sub -- Regards, Tom Ogilvy "dan" wrote: How can I create a macro which will lookup values from one sheet in another, and copy corresponding values? Example: Sheet 1: ColA ColB Dogs Cats Pigs Sheet 2 ColA ColB Dogs 125 Gerbils 392 Cats 92 Pigs 423 Horses 32 I want it to look up if Sheet2 has 'Dogs' in column A, then to copy the value in of Dogs in B2 (125)into the corresponding column in Sheet1 columnB where Dogs is listed. It would ignore gerbils and horses, but put the values from Sheet2 in Sheet1 column B accordingly. Thank you for your help in advance!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
One sheet to another..
Thanks!!
Vito wrote: You shouldn't need a macro for that: Just use Vlookup() function. In Sheet1, B2 enter: =Vlookup(A2,Sheet2!A:B,2,0) then copy the formula down column the column. -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=558620 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel sheet bootom half sheet goes behind top part of sheet | Excel Worksheet Functions | |||
Duplicate sheet, autonumber sheet, record data on another sheet | Excel Worksheet Functions | |||
Copying cells from on sheet to another sheet (via sheet module) | Excel Programming | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) | |||
Inserting a row in sheet A should Insert a row in sheet B, removing a row in Sheet A should remove the corresponding row in sheet B | Excel Programming |