| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
replacing value between sheets!
hi all! i am having two worksheets. sheet1COL"A" having some defined values & sheet1COL"B" having some related defined names. sheet2 COL"A" having the same defined values as in sheet1 but randomly repetitive and COL"B" having names but entirely different with sheet1COL"B". what i want is to replace the names in sheet2COL"B" with the related difined names as in sheet1COL"B"! example sheet1 COL"A" COL"B" 10 aaa 20 bbb 30 ccc 40 ddd 50 eee sheet2 COL"A" COL"B" 10 abc 20 bca 10 cab 30 cba 40 xyz 50 yzx 20 mno result expected sheet2 COL"A" COL"B" 10 aaa 20 bbb 10 aaa 30 ccc 40 ddd 50 eee 20 bbb help pl? -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=508493 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
replacing value between sheets!
in col b sheet 2
=vlookup(a1,sheet1!$A$1:$B$5,2,false),and copy down.Adjust the table a1:b5 to reflect the actual size of your tble but you must use the $ symbols to make an absolute reference to the table -- paul remove nospam for email addy! "via135" wrote: hi all! i am having two worksheets. sheet1COL"A" having some defined values & sheet1COL"B" having some related defined names. sheet2 COL"A" having the same defined values as in sheet1 but randomly repetitive and COL"B" having names but entirely different with sheet1COL"B". what i want is to replace the names in sheet2COL"B" with the related difined names as in sheet1COL"B"! example sheet1 COL"A" COL"B" 10 aaa 20 bbb 30 ccc 40 ddd 50 eee sheet2 COL"A" COL"B" 10 abc 20 bca 10 cab 30 cba 40 xyz 50 yzx 20 mno result expected sheet2 COL"A" COL"B" 10 aaa 20 bbb 10 aaa 30 ccc 40 ddd 50 eee 20 bbb help pl? -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=508493 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
replacing value between sheets!
sorry Paul! i am getting error #REF! what's wrong with me? -via135 paul Wrote: in col b sheet 2 =vlookup(a1,sheet1!$A$1:$B$5,2,false),and copy down.Adjust the table a1:b5 to reflect the actual size of your tble but you must use the $ symbols to make an absolute reference to the table -- paul remove nospam for email addy! "via135" wrote: hi all! i am having two worksheets. sheet1COL"A" having some defined values & sheet1COL"B" having some related defined names. sheet2 COL"A" having the same defined values as in sheet1 but randomly repetitive and COL"B" having names but entirely different with sheet1COL"B". what i want is to replace the names in sheet2COL"B" with the related difined names as in sheet1COL"B"! example sheet1 COL"A" COL"B" 10 aaa 20 bbb 30 ccc 40 ddd 50 eee sheet2 COL"A" COL"B" 10 abc 20 bca 10 cab 30 cba 40 xyz 50 yzx 20 mno result expected sheet2 COL"A" COL"B" 10 aaa 20 bbb 10 aaa 30 ccc 40 ddd 50 eee 20 bbb help pl? -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=508493 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=508493 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
replacing value between sheets!
What formula are you using? #REF! possibly means that the sheet name is not recognised. Check you are referencing the exact sheet name. If sheet name contains spaces use single quotes around sheet name as below =vlookup(a1,'sheet 1'!$A$1:$B$5,2,false) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=508493 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
replacing value between sheets!
hi! i am using the exact sheet name! and my formula is =(VLOOKUP(A1,Sheet7!$A$1:$A$6,2,FALSE)) can u help me pl? -via135 daddylonglegs Wrote: What formula are you using? #REF! possibly means that the sheet name is not recognised. Check you are referencing the exact sheet name. If sheet name contains spaces use single quotes around sheet name as below =vlookup(a1,'sheet 1'!$A$1:$B$5,2,false) -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=508493 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
replacing value between sheets!
That formula can never return anything but an error, however that error
should be #N/A You are trying to return a value in the second column from a table that is only one column If you want the 2nd column you need to use =VLOOKUP(A1,Sheet7!$A$1:$B$6,2,FALSE) now you can get ref errors if you delete a row where a formula points to, if you use INDIRECT incorrectly or if you already have a ref error in a range you are using. You might want to check that, the version of your formula that I posted works -- Regards, Peo Sjoblom Portland, Oregon "via135" wrote in message ... hi! i am using the exact sheet name! and my formula is =(VLOOKUP(A1,Sheet7!$A$1:$A$6,2,FALSE)) can u help me pl? -via135 daddylonglegs Wrote: What formula are you using? #REF! possibly means that the sheet name is not recognised. Check you are referencing the exact sheet name. If sheet name contains spaces use single quotes around sheet name as below =vlookup(a1,'sheet 1'!$A$1:$B$5,2,false) -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=508493 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
replacing value between sheets!
Your table is only one column wide ($A$1:$A$6), yet you are trying to
get data from the second column. I suggest you change the formula to: =(VLOOKUP(A1,Sheet7!$A$1:$B$6,2,FALSE)) Hope this helps. Pete |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
replacing value between sheets!
thank you all for pinpointing my stupid mistake! after adjusting my array ref to 2 col ($a$1:$b$6) the formula works nicely! thks again! -via135 Pete Wrote: Your table is only one column wide ($A$1:$A$6), yet you are trying to get data from the second column. I suggest you change the formula to: =(VLOOKUP(A1,Sheet7!$A$1:$B$6,2,FALSE)) Hope this helps. Pete -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=508493 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Autofilter Lists across Multiple Sheets, Maintain Correct Referenc | Excel Worksheet Functions | |||
| In 3 active sheets in wkbk, determine& display the # of sheets that have data | Excel Discussion (Misc queries) | |||
| Finding specific sheets within a workbook | Excel Discussion (Misc queries) | |||
| calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
| Multiple sheets selected | Excel Discussion (Misc queries) | |||
Linear Mode
