software

hi all!

i am having two worksheets. sheet1COLquot;Aquot; having some defined values amp;
sheet1COLquot;Bquot; having some related defined names.

sheet2 COLquot;Aquot; having the same defined values as in sheet1 but randomly
repetitive and COLquot;Bquot; having names but entirely different with
sheet1COLquot;Bquot;.

what i want is to replace the names in sheet2COLquot;Bquot; with the related
difined names as in sheet1COLquot;Bquot;!

example

sheet1
COLquot;Aquot; COLquot;Bquot;
10aaa
20bbb
30ccc
40ddd
50eee

sheet2
COLquot;Aquot;COLquot;Bquot;
10abc
20bca
10cab
30cba
40xyz
50yzx
20mnoresult expected
sheet2
COLquot;Aquot;COLquot;Bquot;
10aaa
20bbb
10aaa
30ccc
40ddd
50eee
20bbb
help pl?

-via135--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=508493in 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!
quot;via135quot; wrote:

gt;
gt; hi all!
gt;
gt; i am having two worksheets. sheet1COLquot;Aquot; having some defined values amp;
gt; sheet1COLquot;Bquot; having some related defined names.
gt;
gt; sheet2 COLquot;Aquot; having the same defined values as in sheet1 but randomly
gt; repetitive and COLquot;Bquot; having names but entirely different with
gt; sheet1COLquot;Bquot;.
gt;
gt; what i want is to replace the names in sheet2COLquot;Bquot; with the related
gt; difined names as in sheet1COLquot;Bquot;!
gt;
gt; example
gt;
gt; sheet1
gt; COLquot;Aquot; COLquot;Bquot;
gt; 10aaa
gt; 20bbb
gt; 30ccc
gt; 40ddd
gt; 50eee
gt;
gt; sheet2
gt; COLquot;Aquot;COLquot;Bquot;
gt; 10abc
gt; 20bca
gt; 10cab
gt; 30cba
gt; 40xyz
gt; 50yzx
gt; 20mno
gt;
gt;
gt; result expected
gt; sheet2
gt; COLquot;Aquot;COLquot;Bquot;
gt; 10aaa
gt; 20bbb
gt; 10aaa
gt; 30ccc
gt; 40ddd
gt; 50eee
gt; 20bbb
gt;
gt;
gt;
gt; help pl?
gt;
gt; -via135
gt;
gt;
gt; --
gt; via135
gt; ------------------------------------------------------------------------
gt; via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
gt; View this thread: www.excelforum.com/showthread...hreadid=508493
gt;
gt;


sorry Paul!

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

-via135

paul Wrote:
gt; in col b sheet 2
gt; =vlookup(a1,sheet1!$A$1:$B$5,2,false),and copy down.Adjust the table
gt; a1:b5
gt; to reflect the actual size of your tble but you must use the $ symbols
gt; to
gt; make an absolute reference to the table
gt; --
gt; paul
gt; remove nospam for email addy!
gt;
gt;
gt;
gt; quot;via135quot; wrote:
gt;
gt; gt;
gt; gt; hi all!
gt; gt;
gt; gt; i am having two worksheets. sheet1COLquot;Aquot; having some defined values
gt; amp;
gt; gt; sheet1COLquot;Bquot; having some related defined names.
gt; gt;
gt; gt; sheet2 COLquot;Aquot; having the same defined values as in sheet1 but
gt; randomly
gt; gt; repetitive and COLquot;Bquot; having names but entirely different with
gt; gt; sheet1COLquot;Bquot;.
gt; gt;
gt; gt; what i want is to replace the names in sheet2COLquot;Bquot; with the related
gt; gt; difined names as in sheet1COLquot;Bquot;!
gt; gt;
gt; gt; example
gt; gt;
gt; gt; sheet1
gt; gt; COLquot;Aquot; COLquot;Bquot;
gt; gt; 10aaa
gt; gt; 20bbb
gt; gt; 30ccc
gt; gt; 40ddd
gt; gt; 50eee
gt; gt;
gt; gt; sheet2
gt; gt; COLquot;Aquot;COLquot;Bquot;
gt; gt; 10abc
gt; gt; 20bca
gt; gt; 10cab
gt; gt; 30cba
gt; gt; 40xyz
gt; gt; 50yzx
gt; gt; 20mno
gt; gt;
gt; gt;
gt; gt; result expected
gt; gt; sheet2
gt; gt; COLquot;Aquot;COLquot;Bquot;
gt; gt; 10aaa
gt; gt; 20bbb
gt; gt; 10aaa
gt; gt; 30ccc
gt; gt; 40ddd
gt; gt; 50eee
gt; gt; 20bbb
gt; gt;
gt; gt;
gt; gt;
gt; gt; help pl?
gt; gt;
gt; gt; -via135
gt; gt;
gt; gt;
gt; gt; --
gt; gt; via135
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; via135's Profile:
gt; www.excelforum.com/member.php...oamp;userid=26725
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=508493
gt; gt;
gt; gt;--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=508493
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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=508493
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:
gt; What formula are you using?
gt;
gt; #REF! possibly means that the sheet name is not recognised. Check you
gt; are referencing the exact sheet name. If sheet name contains spaces use
gt; single quotes around sheet name as below
gt;
gt; =vlookup(a1,'sheet 1'!$A$1:$B$5,2,false)--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=508493That 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

quot;via135quot; gt; wrote in
message ...
gt;
gt; hi!
gt;
gt; i am using the exact sheet name! and my formula is
gt;
gt; =(VLOOKUP(A1,Sheet7!$A$1:$A$6,2,FALSE))
gt;
gt; can u help me pl?
gt;
gt; -via135
gt;
gt; daddylonglegs Wrote:
gt;gt; What formula are you using?
gt;gt;
gt;gt; #REF! possibly means that the sheet name is not recognised. Check you
gt;gt; are referencing the exact sheet name. If sheet name contains spaces use
gt;gt; single quotes around sheet name as below
gt;gt;
gt;gt; =vlookup(a1,'sheet 1'!$A$1:$B$5,2,false)
gt;
gt;
gt; --
gt; via135
gt; ------------------------------------------------------------------------
gt; via135's Profile:
gt; www.excelforum.com/member.php...oamp;userid=26725
gt; View this thread: www.excelforum.com/showthread...hreadid=508493
gt;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
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:
gt; Your table is only one column wide ($A$1:$A$6), yet you are trying to
gt; get data from the second column. I suggest you change the formula to:
gt;
gt; =(VLOOKUP(A1,Sheet7!$A$1:$B$6,2,FALSE))
gt;
gt; Hope this helps.
gt;
gt; Pete--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=508493