Prev: How to Import data from a different .xls file
Next: Merging reports
From: Cesar Urquidi on 18 May 2010 13:48
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
From: Luke M on 18 May 2010 14:09
Change the source formula in column C to:
=IF(COUNTA(A2:B2)<>2,"",A2&" Qty. "&B2)

--
Best Regards,

Luke M
"Cesar Urquidi" <CesarUrquidi(a)discussions.microsoft.com> wrote in message
news:8686F113-A340-4635-9C4B-36C590F0E551(a)microsoft.com...
> 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


From: Cesar Urquidi on 18 May 2010 15:02
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" <CesarUrquidi(a)discussions.microsoft.com> wrote in message
> news:8686F113-A340-4635-9C4B-36C590F0E551(a)microsoft.com...
> > 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
>
>
> .
>
 | 
Pages: 1
Prev: How to Import data from a different .xls file
Next: Merging reports