Matching records in diff columns

[Newtonboy]

I have three worksheets,

1. Stock Produced
2. Stock in Port
3. Stock Shipped.

All the stocks which we hold are in teh Sheet Stock Produced.

can i have a formula in excel, whereby as soon as i enter the PartID in "Stock in Port" sheet, a column in teh Stock Sheet says, Stock in Port. And Once i enter the PartID in Shipped Sheet, the column reads "stock Shipped" and the PartID disappears from "Stock in Port" sheet.

[Bob Phillips]

In an adjacent column in the Produced sheet, add

=IF(NOT(ISNA(MATCH(A2,'Stock In Port'!A:A,0))),"Stock In
Part",IF(NOT(ISNA(MATCH(A2,'Stock Shipped'!A:A,0))),"Stock Shipped",""))

and copy down.

Then if in the Stock In Port sheet, add conditional formatting. Select
column A, goto CF, change Condition 1 to Formula Is and add the formula
=NOT(ISNA(MATCH(A1,'Stock Shipped'!A:A,0))) and select the Font tab and
choose a font colour of white, then exit.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Newtonboy" <[email protected]> wrote
in message news:[email protected]...
>
> I have three worksheets,
>
> 1. Stock Produced
> 2. Stock in Port
> 3. Stock Shipped.
>
> All the stocks which we hold are in teh Sheet Stock Produced.
>
> can i have a formula in excel, whereby as soon as i enter the PartID in
> "Stock in Port" sheet, a column in teh Stock Sheet says, Stock in Port.
> And Once i enter the PartID in Shipped Sheet, the column reads "stock
> Shipped" and the PartID disappears from "Stock in Port" sheet.
>
>
> --
> Newtonboy
> ------------------------------------------------------------------------
> Newtonboy's Profile:
http://www.excelforum.com/member.php...o&userid=29582
> View this thread: http://www.excelforum.com/showthread...hreadid=560983
>

[Newtonboy]

Thanx for the response, But excel is coming up with with an error message saying something is missing in the formula, when i am doing conditional formatting.

Pls Help

[Bob Phillips]

Sorry, CF cannot refer (directly) to another sheet.

Create a name (Insert>Name>Define...) with a name of StockShipped and a
RefersTo value of 'Stock Shipped'!A:A. Then in CF, use a formula of
=NOT(ISNA(MATCH(A1,StockShipped,0)))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Newtonboy" <[email protected]> wrote
in message news:[email protected]...
>
> Thanx for the response, But excel is coming up with with an error
> message saying something is missing in the formula, when i am doing
> conditional formatting.
>
> Pls Help
>
>
> --
> Newtonboy
> ------------------------------------------------------------------------
> Newtonboy's Profile:
http://www.excelforum.com/member.php...o&userid=29582
> View this thread: http://www.excelforum.com/showthread...hreadid=560983
>