replacing value between sheets!

V

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
 
P

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
 
D

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

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
 
P

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
 
P

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
 
V

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top