Matching records in diff columns

  • Thread starter Thread starter Newtonboy
  • Start date Start date
N

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.
 
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)
 
Thanx for the response, But excel is coming up with with an erro
message saying something is missing in the formula, when i am doin
conditional formatting.

Pls Hel
 
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)
 
Back
Top