Conditional formatting

P

phill

Hi

I have a spreadsheet which i use to keep a record of stock in a shop and it
has two tabs one for stock in store and another for obsolete stock. on sheet
one i have a list of product codes and a description and on sheet two there
is the same. how can i make the colums on sheet one ie. the product code and
description show up red if the product appears on sheet 2?

many thanks

phill
 
S

Stefi

Hi Phill,

First insert a name, say Obsolete, and define its reference like Sheet2!A:A
(say product code is in column A)

Select Sheet1 Column A, Conditional formatting/Formula:
=NOT(ISERROR(VLOOKUP(A1,obsolete,1,FALSE)))
Choose red color, OK

Regards,
Stefi


„phill†ezt írta:
 
D

Dav

You use conditional formating, but unfortunately it can not directl
access a range on the other sheet, so firstly you have to name thi
range, it may be better to make it larger than your data currently t
allow for expansion

you do this by insert name define if you called this range test and o
the other sheet were looking at the value in cell a1.

Goto conditional formating choose formula is and type
=VLOOKUP(A1,test,1,FALSE)>0 and format the cell as red

This format can be pasted as a format to the other cells where you nee
to apply the logic

Regards

Da
 
L

Leo Heuser

phill said:
Hi

I have a spreadsheet which i use to keep a record of stock in a shop and
it
has two tabs one for stock in store and another for obsolete stock. on
sheet
one i have a list of product codes and a description and on sheet two
there
is the same. how can i make the colums on sheet one ie. the product code
and
description show up red if the product appears on sheet 2?

many thanks

phill
Click to expand...

Hi Phill

One way:

Assumptions: Product codes in columns A and
descriptions in columns B.

1. On sheet 2 select column A (click on the letter "A" on top
of the column)
2. Click in the name box (extreme left of the formula bar)
and enter a name for the selected range, e.g. "Data"
without quotes.
3. On sheet 1 select columns A and B as described.
4. Choose Formats > Conditional formatting
5. In "Formula is" enter the formula =COUNTIF(Data,$A1)
(Notice that only the column is absolute ($).
6. Choose the wanted format with the button "Formats"
and OK. OK.

Points 1 and 2 are necessary because a formula in
conditional formatting must point to a global name.
=COUNTIF(Sheet2!A:A,$A1) won't work because
Sheet2!A:A is a local address, not a global one.
The same holds, if you have validation and the list
is on another sheet.
 
Top