I have a cell in worksheet2 with reference to worksheet1.
I try to use conditional formatting but it give me error stating
"Cannot use references to other worksheet for conditional formatting
criteria".

How to solve this problem? Is there a way out?

Thank you.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Michael,

You have found a limit on CF. Luckily it can be easily circumvented. Define
the data on Worksheet1 as a workbook named range, and use that name in the
CF.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Michael168" wrote in message
...
I have a cell in worksheet2 with reference to worksheet1.
I try to use conditional formatting but it give me error stating
"Cannot use references to other worksheet for conditional formatting
criteria".

How to solve this problem? Is there a way out?

Thank you.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

I still have problem.
Range define as CFR=Sheet1!A10:F10 in sheet2
When I do conditional format in Sheet2, it seems the CFR can only take
Sheet1 column A range only and omits all the ranges for B:F.

Please kindly show me how to go about.

Thank you.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Michael,

It depends upon the formula you are using.

Post the formula, and the values being tested.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Michael168" wrote in message
...
I still have problem.
Range define as CFR=Sheet1!A10:F10 in sheet2
When I do conditional format in Sheet2, it seems the CFR can only take
Sheet1 column A range only and omits all the ranges for B:F.

Please kindly show me how to go about.

Thank you.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

your definition should be

=Sheet1!$A$10:$F$10

That is a possible source of your problem.

Assuming your trying to match your numbers against those in CFR


=Match(A1,CFR,0)

would be possibility for Cell A1 in Sheet2

--
Regards,
Tom Ogilvy



Michael168 wrote in message
...
I still have problem.
Range define as CFR=Sheet1!A10:F10 in sheet2
When I do conditional format in Sheet2, it seems the CFR can only take
Sheet1 column A range only and omits all the ranges for B:F.

Please kindly show me how to go about.

Thank you.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Problem still unsolve.

In sheet2 G1 I have this formula
=(COUNTIF(Sheet1!$A10:$F10,1)0)*COUNT(MATCH({31,3 2,33},Sheet1!$A11:$F11,0))

Range define as CFR=Sheet1!$A10:$F10 in sheet2
The CFR still only take Sheet1 column A range only and omits all the
ranges for B:F. I need to go through the worksheet that's why I keep
the row relative.

As suggested by Tom, CFR=Sheet1!$A$10:$F$10, CFR also the same.

Please try to help.

Thank you.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/