Conditional formatting

[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

[Dav]

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

you do this by insert name define if you called this range test and on 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 need to apply the logic

Regards

Dav

[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:

> 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

[Leo Heuser]

"phill" <[email protected]> skrev i en meddelelse
news:[email protected]...
> 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

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.


--
Best regards
Leo Heuser

Followup to newsgroup only please.