| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I select price from sheet.b where sheet.a part no = sheet.b
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I select price from sheet.b where sheet.a part no = sheet.b
=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) "Sonny" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I select price from sheet.b where sheet.a part no = she
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. "Bob Phillips" wrote: =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) "Sonny" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I select price from sheet.b where sheet.a part no = she
My fault mat, my keyboard plays up occasionally and replaces " by @. It
should be =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) "Sonny" wrote in message ... 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. "Bob Phillips" wrote: =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) "Sonny" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I select price from sheet.b where sheet.a part no = she
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. "Bob Phillips" wrote: My fault mat, my keyboard plays up occasionally and replaces " by @. It should be =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) "Sonny" wrote in message ... 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. "Bob Phillips" wrote: =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) "Sonny" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Transpose unique values in one column/mult. rows into a single row | Excel Worksheet Functions | |||
| select part of a cell value string | Excel Discussion (Misc queries) | |||
| Conversion | Excel Worksheet Functions | |||
| Select part of a cell | Excel Worksheet Functions | |||
| Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
Hybrid Mode
