Excel 2007 : Conditional formatting

[azz]

hi, all,

Thank you for your kind help.

I hope to do a conditional format to see if my results change or not. When I design the simple conditional formatting rule, Excel 2007 says that "reference cell can not in different worksheet!". I am using Excel sp1.

Can any expert help me out?

appreciate it.

Azz

[NBVC]

You can name the cells in the other sheet and refer to them by name.. or you can refer to them indirectly... e.g..'data2'!c5>Indirect("'data1'!b3")

[azz]

Thanks for your reply.

But if I have a 10*10 range, I have to name or use indirect one by one.

[NBVC]

Please upload a better sample and show exactly what you want to do... the sample posted doesn't really tell me what you want.

[azz]

Thank you again. Sorry for the previous data.

I have a matrix of origonal data, say, 500 by 50. I calc their var/cov matrix, which is 50 by 50.

When I simulate the origonal data, so the var/cov matrix changes. I need to compare how the individual var or cov changes.

I want to conditionaly format my simu'd matrix by comparing the origonal var/cov matrix.

The dataset is large and I do not want them in one worksheet, that is my problem.

Thanks.

[NBVC]

Sorry for being dense, but I am not understanding what you want coloured and when. Give some examples of what will be coloured on what sheet and why.

[azz]

for the original dataset, I have a var/cov matrix A.
then I change some of the original data by simulation, recalc a var/cov matrix B. B has the same dimension as A, and is different than A.

I hope to color-code those cells in B, which is different than the corresponding values in A.

Appreciate your help.

[NBVC]

I am a little confused as you are not giving examples of what should/should not be coloured.. by your description I can't see any that should not be coloured....

Anyways, what I did was name the range in sheet origonol A1:L15 as "Original" without quotes...

then I selected Sim1, F7:P20 and applied conditional format formula:

=F7<>OFFSET(Original,ROW(A1),COLUMN(A1),1,1)

similar for Sim2 range...

Is that right? All cells got highlighted.

[azz]

Sorry, my bad.

Please check out the formatting I hope. Really appreciate.

[SDruley]

I have fixed your matrix comparison scenario with intra worksheet formatting. Please see attached file.

[azz]

Hi, SD,

Exactly what I hoped. Can you hint me how you did it? I tried but did not figure it out.

Thanks a lot.

[shg]

Select F13 on sim1, and click Conditional Formatting on the Home tab to see the rules applied.

[azz]

Got it. Thank you guys so much.

[SDruley]

azz,

I know that marking the post SOLVED is not a straight forward thing to do but could you do so?

sdruley

[NBVC]

azz can no longer change that...as it has been past 2 days...

I changed it to [SOLVED]