Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find and copy cells
suppose i hav two sheets as follows...
SHEET 1 ID location country 1 211 2 315 3 234 4 146 5 254 6 147 7 631 8 942 9 845 10 413 and sheet 2 as id name customer location region 1 211 steve ford onsite usa 2 315 alex nissan offshore india 3 631 white gm onsite usa 4 146 rishi tata onshore india 5 254 john ford offshore india 6 147 bill ford offshore india 7 295 george nissan onshore usa 8 942 enayan gm onshore usa 9 845 mike tata offshore usa 10 413 ali gm onsite india 11 456 murray gm onsite usa 12 486 tim tata offshore india 13 546 david tata onsite inida 14 896 micheal nissan onsite usa 15 234 alison nissan onsite france now i have to search sheet2 and fill up sheet 1 with the corresponding entries....both the sheets are in the same workbook... if someone can help me it would be great... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find and copy cells
use Vlookup rather than a macro, eg in sheet1, location column:
=vlookup(A1,sheet2!A1:E65000,4,FALSE) "rishi" wrote: suppose i hav two sheets as follows... SHEET 1 ID location country 1 211 2 315 3 234 4 146 5 254 6 147 7 631 8 942 9 845 10 413 and sheet 2 as id name customer location region 1 211 steve ford onsite usa 2 315 alex nissan offshore india 3 631 white gm onsite usa 4 146 rishi tata onshore india 5 254 john ford offshore india 6 147 bill ford offshore india 7 295 george nissan onshore usa 8 942 enayan gm onshore usa 9 845 mike tata offshore usa 10 413 ali gm onsite india 11 456 murray gm onsite usa 12 486 tim tata offshore india 13 546 david tata onsite inida 14 896 micheal nissan onsite usa 15 234 alison nissan onsite france now i have to search sheet2 and fill up sheet 1 with the corresponding entries....both the sheets are in the same workbook... if someone can help me it would be great... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find and copy cells
Hi Rishi, Try using Excel's VLookup funtion. See Debra Dalgleish's tutorial at: Excel -- Worksheet Functions -- VLookup http://www.contextures.com/xlFunctions02.html --- Regards, Norman "rishi" wrote in message oups.com... suppose i hav two sheets as follows... SHEET 1 ID location country 1 211 2 315 3 234 4 146 5 254 6 147 7 631 8 942 9 845 10 413 and sheet 2 as id name customer location region 1 211 steve ford onsite usa 2 315 alex nissan offshore india 3 631 white gm onsite usa 4 146 rishi tata onshore india 5 254 john ford offshore india 6 147 bill ford offshore india 7 295 george nissan onshore usa 8 942 enayan gm onshore usa 9 845 mike tata offshore usa 10 413 ali gm onsite india 11 456 murray gm onsite usa 12 486 tim tata offshore india 13 546 david tata onsite inida 14 896 micheal nissan onsite usa 15 234 alison nissan onsite france now i have to search sheet2 and fill up sheet 1 with the corresponding entries....both the sheets are in the same workbook... if someone can help me it would be great... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
find and copy cells
It's not easy to see exacly how your data are laid out so I've made some
assumptions. ID on sheet1 is in column A and your data in sheet2 are in columns A to E and Location is in column 4 and country is in column 5. So try:- =VLOOKUP(A2,Sheet2!A2:E16,4,FALSE) placed in B2 sheet1 to return loc =VLOOKUP(A2,Sheet2!A2:E16,5,FALSE) placed in C2 sheet1 to return city etc Mike "rishi" wrote: suppose i hav two sheets as follows... SHEET 1 ID location country 1 211 2 315 3 234 4 146 5 254 6 147 7 631 8 942 9 845 10 413 and sheet 2 as id name customer location region 1 211 steve ford onsite usa 2 315 alex nissan offshore india 3 631 white gm onsite usa 4 146 rishi tata onshore india 5 254 john ford offshore india 6 147 bill ford offshore india 7 295 george nissan onshore usa 8 942 enayan gm onshore usa 9 845 mike tata offshore usa 10 413 ali gm onsite india 11 456 murray gm onsite usa 12 486 tim tata offshore india 13 546 david tata onsite inida 14 896 micheal nissan onsite usa 15 234 alison nissan onsite france now i have to search sheet2 and fill up sheet 1 with the corresponding entries....both the sheets are in the same workbook... if someone can help me it would be great... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find match between cells and copy data | Excel Worksheet Functions | |||
Copy contents of Find (Find and Replace) | Excel Discussion (Misc queries) | |||
how do we copy all the find cells with their rows in the excel? | Excel Discussion (Misc queries) | |||
find and copy cells to different workbook | Excel Programming | |||
Search, find, copy muliple cells from new sheet and paste | Excel Programming |