Cell highlighting question

[gootroots]

I need formula to highlight cells based on values contained in another sheet
in the same workbook.

Sheet1
A1 = Product Name
A2:A100 contains unsorted unique values
B1:Q1 contains unsorted unique headings
B2:Q100 have sporadic cells containing a value (e.g. D3, F18, G22, J86...
etc)

Sheet2
A1 = heading - Product Name
A2:A100 contains unsorted unique values
B1:Q1 contains unsorted unique headings
B2:Q100 its irrelevant if any cell contain a value

I need a conditional format in sheet2 B2:Q100 to search in sheet1 B2:Q100
and highlight any cells containing a value

For example:

The value "Tractor" can be found in sheet1 A43 and also in sheet2 A23

The heading "PartNo" can be found in sheet1 D1 and also in sheet2 H1

A formula locates and matches both "Tractor" and "PartNo" in sheet1

Any value found to be in Sheet1 B43:Q43 will then have the relevant cells in
sheet2 B23:Q23 highlighted

 

[Bernard Liengme]

If all you need is to locate on B2:Q100 of Sheet2 any value that appears in
the same range on Sheet1 without reference to the row and column headers,
then use
Formula Is =COUNTIF(mydata,C2)>0
Conditional Formatting does not allow you to reference a range in another
sheet, so you must first select B2:Q100 in Sheet1 and type a name like
MyData into the name AND PRESS ENTER.

If this does not answer the question please tell us if Tractor on the same
row and PartNo in a different column are part and parcel of the requirement.

best wishes

 

[Squeaky]

Hi gootroots,

Since you cannot reference another sheet with conditional formatting what I
do is set up a set of hidden cells. On sheet 2 write a small "if" formula
referencing sheet 1 that will say "yes" when your criteria is met and and
"no" when it is not. You can then reference those cells to change your
colors.

Make sense?

Squeaky

 

[Gotroots]

Thank you guys for the help

I have followed your advice Squeaky and will use hidden cells.
The following formula in D23 (the hidden cell) returns a True or False
result for a Product match


=IF(ISNA(MATCH(A23,sheet1!$A:$A,0)),IF(ISNA(MATCH(A23,sheet1!A:A,0)),"No
Match Found",A23),A23)

I need to know what formula is needed to reference a formula result when
using conditional formatting. Cell E23 will change colour if D23 is True.

 

[Squeaky]

In cell E3, select conditional formatting (or Manage Rules in 2007). In the
formula statement put =D3=true. select your color formatting.

Squeaky