replacing value between sheets!

[via135]

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

[paul]

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]

sorry Paul!

i am getting error #REF!
what's wrong with me?

-via135

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
>
>

[daddylonglegs]

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]

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

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)

[Peo Sjoblom]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Pete]

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]

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

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