software

Hi all,
how do I select price from sheet B where sheet A part no is equal to sheet B
part no. E.g.
I have a list on worksheet A with no price.
SHEET A
PartNumber Description Price
0000001
0000002
0000003
0000004
0000005
0000006
0000007

I have a list on worksheet B with values for price, I need these prices for
the same part no as on worksheet A to populate the price column on worksheet
A.
SHEET B
PartNumber Price
0000001 1.50
0000002 0.80
0000003 0.30
0000004 2.50
0000005 6.00
0000006 3.80
0000007 1.20

I cannot simply sort and copy the price information, as there are many
inconsistencies between these two worksheet some of the part no's may be
there some may not, but for any that are there I need the price.

Regs,
Sonny.
=IF(ISNA(vlookup('SHEET B'!A1:B20,A2,2,False)),@@,vlookup('SHEET
B'!A1:B20,A2,2,False))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Sonnyquot; gt; wrote in message
...
gt; Hi all,
gt; how do I select price from sheet B where sheet A part no is equal to sheet
B
gt; part no. E.g.
gt; I have a list on worksheet A with no price.
gt; SHEET A
gt; PartNumber Description Price
gt; 0000001
gt; 0000002
gt; 0000003
gt; 0000004
gt; 0000005
gt; 0000006
gt; 0000007
gt;
gt; I have a list on worksheet B with values for price, I need these prices
for
gt; the same part no as on worksheet A to populate the price column on
worksheet
gt; A.
gt; SHEET B
gt; PartNumber Price
gt; 0000001 1.50
gt; 0000002 0.80
gt; 0000003 0.30
gt; 0000004 2.50
gt; 0000005 6.00
gt; 0000006 3.80
gt; 0000007 1.20
gt;
gt; I cannot simply sort and copy the price information, as there are many
gt; inconsistencies between these two worksheet some of the part no's may be
gt; there some may not, but for any that are there I need the price.
gt;
gt; Regs,
gt; Sonny.
gt;
gt;
Hi Bob,
thank you very much for the formula, however I am having difficulty in
getting it to work. I beleive the two '@@' at signs are causing the problem.
I am getting an error box appearing saying the formula you typed contains an
error. I have copied and pasted the formula exactly as you specified below.
Hope you can shed some light on this, sorry but I am a bit of a novice as
far as formulas are concerned..

Many thanks for your help.
Sonny.

quot;Bob Phillipsquot; wrote:

gt; =IF(ISNA(vlookup('SHEET B'!A1:B20,A2,2,False)),@@,vlookup('SHEET
gt; B'!A1:B20,A2,2,False))
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Sonnyquot; gt; wrote in message
gt; ...
gt; gt; Hi all,
gt; gt; how do I select price from sheet B where sheet A part no is equal to sheet
gt; B
gt; gt; part no. E.g.
gt; gt; I have a list on worksheet A with no price.
gt; gt; SHEET A
gt; gt; PartNumber Description Price
gt; gt; 0000001
gt; gt; 0000002
gt; gt; 0000003
gt; gt; 0000004
gt; gt; 0000005
gt; gt; 0000006
gt; gt; 0000007
gt; gt;
gt; gt; I have a list on worksheet B with values for price, I need these prices
gt; for
gt; gt; the same part no as on worksheet A to populate the price column on
gt; worksheet
gt; gt; A.
gt; gt; SHEET B
gt; gt; PartNumber Price
gt; gt; 0000001 1.50
gt; gt; 0000002 0.80
gt; gt; 0000003 0.30
gt; gt; 0000004 2.50
gt; gt; 0000005 6.00
gt; gt; 0000006 3.80
gt; gt; 0000007 1.20
gt; gt;
gt; gt; I cannot simply sort and copy the price information, as there are many
gt; gt; inconsistencies between these two worksheet some of the part no's may be
gt; gt; there some may not, but for any that are there I need the price.
gt; gt;
gt; gt; Regs,
gt; gt; Sonny.
gt; gt;
gt; gt;
gt;
gt;
gt;

My fault mat, my keyboard plays up occasionally and replaces quot; by @. It
should be

=IF(ISNA(vlookup('SHEET B'!A1:B20,A2,2,False)),quot;quot;,
vlookup('SHEET B'!A1:B20,A2,2,False))--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Sonnyquot; gt; wrote in message
news
gt; Hi Bob,
gt; thank you very much for the formula, however I am having difficulty in
gt; getting it to work. I beleive the two '@@' at signs are causing the
problem.
gt; I am getting an error box appearing saying the formula you typed contains
an
gt; error. I have copied and pasted the formula exactly as you specified
below.
gt; Hope you can shed some light on this, sorry but I am a bit of a novice as
gt; far as formulas are concerned..
gt;
gt; Many thanks for your help.
gt; Sonny.
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; =IF(ISNA(vlookup('SHEET B'!A1:B20,A2,2,False)),@@,vlookup('SHEET
gt; gt; B'!A1:B20,A2,2,False))
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Sonnyquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi all,
gt; gt; gt; how do I select price from sheet B where sheet A part no is equal to
sheet
gt; gt; B
gt; gt; gt; part no. E.g.
gt; gt; gt; I have a list on worksheet A with no price.
gt; gt; gt; SHEET A
gt; gt; gt; PartNumber Description Price
gt; gt; gt; 0000001
gt; gt; gt; 0000002
gt; gt; gt; 0000003
gt; gt; gt; 0000004
gt; gt; gt; 0000005
gt; gt; gt; 0000006
gt; gt; gt; 0000007
gt; gt; gt;
gt; gt; gt; I have a list on worksheet B with values for price, I need these
prices
gt; gt; for
gt; gt; gt; the same part no as on worksheet A to populate the price column on
gt; gt; worksheet
gt; gt; gt; A.
gt; gt; gt; SHEET B
gt; gt; gt; PartNumber Price
gt; gt; gt; 0000001 1.50
gt; gt; gt; 0000002 0.80
gt; gt; gt; 0000003 0.30
gt; gt; gt; 0000004 2.50
gt; gt; gt; 0000005 6.00
gt; gt; gt; 0000006 3.80
gt; gt; gt; 0000007 1.20
gt; gt; gt;
gt; gt; gt; I cannot simply sort and copy the price information, as there are many
gt; gt; gt; inconsistencies between these two worksheet some of the part no's may
be
gt; gt; gt; there some may not, but for any that are there I need the price.
gt; gt; gt;
gt; gt; gt; Regs,
gt; gt; gt; Sonny.
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
Hi Bob,
again many thanks for your help..... however I am still find it difficult to
work... seems I now get an error of the type #VAULE!
also after I have typed the formula in a pop-up box appears with Update
Values in its header, then I am to select the sheet where it should get the
data to update the sheet from. I select the correct sheet for it to open and
select values from....... after doing this I get the #VALUE! error.........
any ideas where I am going wrong, sorry to be such a pain.....

and thanks for all your help so far....

Regs,
Sonny.
quot;Bob Phillipsquot; wrote:

gt; My fault mat, my keyboard plays up occasionally and replaces quot; by @. It
gt; should be
gt;
gt; =IF(ISNA(vlookup('SHEET B'!A1:B20,A2,2,False)),quot;quot;,
gt; vlookup('SHEET B'!A1:B20,A2,2,False))
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Sonnyquot; gt; wrote in message
gt; news
gt; gt; Hi Bob,
gt; gt; thank you very much for the formula, however I am having difficulty in
gt; gt; getting it to work. I beleive the two '@@' at signs are causing the
gt; problem.
gt; gt; I am getting an error box appearing saying the formula you typed contains
gt; an
gt; gt; error. I have copied and pasted the formula exactly as you specified
gt; below.
gt; gt; Hope you can shed some light on this, sorry but I am a bit of a novice as
gt; gt; far as formulas are concerned..
gt; gt;
gt; gt; Many thanks for your help.
gt; gt; Sonny.
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; =IF(ISNA(vlookup('SHEET B'!A1:B20,A2,2,False)),@@,vlookup('SHEET
gt; gt; gt; B'!A1:B20,A2,2,False))
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Sonnyquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Hi all,
gt; gt; gt; gt; how do I select price from sheet B where sheet A part no is equal to
gt; sheet
gt; gt; gt; B
gt; gt; gt; gt; part no. E.g.
gt; gt; gt; gt; I have a list on worksheet A with no price.
gt; gt; gt; gt; SHEET A
gt; gt; gt; gt; PartNumber Description Price
gt; gt; gt; gt; 0000001
gt; gt; gt; gt; 0000002
gt; gt; gt; gt; 0000003
gt; gt; gt; gt; 0000004
gt; gt; gt; gt; 0000005
gt; gt; gt; gt; 0000006
gt; gt; gt; gt; 0000007
gt; gt; gt; gt;
gt; gt; gt; gt; I have a list on worksheet B with values for price, I need these
gt; prices
gt; gt; gt; for
gt; gt; gt; gt; the same part no as on worksheet A to populate the price column on
gt; gt; gt; worksheet
gt; gt; gt; gt; A.
gt; gt; gt; gt; SHEET B
gt; gt; gt; gt; PartNumber Price
gt; gt; gt; gt; 0000001 1.50
gt; gt; gt; gt; 0000002 0.80
gt; gt; gt; gt; 0000003 0.30
gt; gt; gt; gt; 0000004 2.50
gt; gt; gt; gt; 0000005 6.00
gt; gt; gt; gt; 0000006 3.80
gt; gt; gt; gt; 0000007 1.20
gt; gt; gt; gt;
gt; gt; gt; gt; I cannot simply sort and copy the price information, as there are many
gt; gt; gt; gt; inconsistencies between these two worksheet some of the part no's may
gt; be
gt; gt; gt; gt; there some may not, but for any that are there I need the price.
gt; gt; gt; gt;
gt; gt; gt; gt; Regs,
gt; gt; gt; gt; Sonny.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;