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

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
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"gootroots" wrote in message
...
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

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.


"Bernard Liengme" wrote:

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
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"gootroots" wrote in message
...
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

.

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

Squeaky



"Gotroots" wrote:

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.


"Bernard Liengme" wrote:

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
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"gootroots" wrote in message
...
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

.

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

"gootroots" wrote:

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