|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 18th, 2010, 06:48 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with a formula, please!!!
Hello,
I have 2 sheets; "Sheet1" & "Sheet2": Sheet1: Column A Column B Column C Part Number Qty. P/N & Qty. 10100 1 10100 Qty. 1 10200 1 10200 Qty. 1 10300 2 10300 Qty. 2 Qty. Note: Column "C" is the result of a formula joining columns "A" & "B". This is the formula: =CONCATENATE(A2," ","Qty. ",B2) The text " Qty. " (SpaceQty.Space) in column "C" (5th row) is the result of the formula, because there is nothing in "Part Number" & "Qty." cells (5th row). Sheet2: Column A P/N & Qty. Formula??? I need a formula to replace " Qty. " (SpaceQty.Space) with blank. Only when the cell contains " Qty. " (SpaceQty.Space). If the cell is "10100 Qty. 1", do not replace. I could use the find and replace, but I can't, because I will protect "Sheet1", and find & replace does not work on protected sheets. Help please!!! Thank you, Cesar Urquidi 
|
|
#2
May 18th, 2010, 07:09 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help with a formula, please!!!
Change the source formula in column C to:
=IF(COUNTA(A2:B2)2,"",A2&" Qty. "&B2) -- Best Regards, Luke M "Cesar Urquidi" wrote in message ... Hello, I have 2 sheets; "Sheet1" & "Sheet2": Sheet1: Column A Column B Column C Part Number Qty. P/N & Qty. 10100 1 10100 Qty. 1 10200 1 10200 Qty. 1 10300 2 10300 Qty. 2 Qty. Note: Column "C" is the result of a formula joining columns "A" & "B". This is the formula: =CONCATENATE(A2," ","Qty. ",B2) The text " Qty. " (SpaceQty.Space) in column "C" (5th row) is the result of the formula, because there is nothing in "Part Number" & "Qty." cells (5th row). Sheet2: Column A P/N & Qty. Formula??? I need a formula to replace " Qty. " (SpaceQty.Space) with blank. Only when the cell contains " Qty. " (SpaceQty.Space). If the cell is "10100 Qty. 1", do not replace. I could use the find and replace, but I can't, because I will protect "Sheet1", and find & replace does not work on protected sheets. Help please!!! Thank you, Cesar Urquidi
|
|
#3
May 18th, 2010, 08:02 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help with a formula, please!!!
Hello Luke,
It worked fine, but if there is a "Part Number" in "A5" with no "Qty." in "B5", then nothing appears in "C5" using your formula. Even if the part number has no qty., I still want to display it. Any other idea?!!! Thank you, Cesar "Luke M" wrote: Change the source formula in column C to: =IF(COUNTA(A2:B2)2,"",A2&" Qty. "&B2) -- Best Regards, Luke M "Cesar Urquidi" wrote in message ... Hello, I have 2 sheets; "Sheet1" & "Sheet2": Sheet1: Column A Column B Column C Part Number Qty. P/N & Qty. 10100 1 10100 Qty. 1 10200 1 10200 Qty. 1 10300 2 10300 Qty. 2 Qty. Note: Column "C" is the result of a formula joining columns "A" & "B". This is the formula: =CONCATENATE(A2," ","Qty. ",B2) The text " Qty. " (SpaceQty.Space) in column "C" (5th row) is the result of the formula, because there is nothing in "Part Number" & "Qty." cells (5th row). Sheet2: Column A P/N & Qty. Formula??? I need a formula to replace " Qty. " (SpaceQty.Space) with blank. Only when the cell contains " Qty. " (SpaceQty.Space). If the cell is "10100 Qty. 1", do not replace. I could use the find and replace, but I can't, because I will protect "Sheet1", and find & replace does not work on protected sheets. Help please!!! Thank you, Cesar Urquidi .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
