Tricky: Conditional Formatting based of Matrix Elements

R

rippchen10247

New Member
Joined
Apr 18, 2013
Messages
18
Hey there,

i have a 5x5 matrix table consisting of some correlation values. The values are not unique, so some of them are mentioned once (e.g. 0), some twice (e.g. 0.1), some even more often (e.g. 0.2).

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]0.2
[/TD]
[TD]0.4
[/TD]
[TD]0.6
[/TD]
[TD]0.8
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]0.15
[/TD]
[TD]0.3
[/TD]
[TD]0.45
[/TD]
[TD]0.6
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]0.1
[/TD]
[TD]0.2
[/TD]
[TD]0.3
[/TD]
[TD]0.4
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]0.0
[/TD]
[TD]0.1
[/TD]
[TD]0.15
[/TD]
[TD]0.2
[/TD]
[/TR]
</tbody>[/TABLE]

What I want to do is to apply conditional formatting always only to one matrix element, no matter, if the value is mentioned once, twice or more often than this..

E.g.: I want to do conditional formatting for the element which is defined by the row 2 & and column 4 element, thus 0.4 should be highlighted through conditional formatting there, but only there and not for the same element which is stated in row 1 column 2.

The whole stuff should be dynamic for rows and columns, so that I can target all single matrix elements for my conditional formatting based on what I chose as row and column.

Thought of =offset =index(,match, match), but was not successful so far :-(

Can someone help me to create an elegant formula?
 
Sort by date Sort by votes

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
rippchen10247 said:
Alright so again, this is the big picture. Lets imagine, i will have sth like this in my excel sheet:

Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting

No image it is not even 4x 5x5 matrices, but at least 25x 5x5 matrices like this. For each matrix, i will have an individual combination, e.g. row1,col5 for first matrix or row3col3 for second. I want to highlight exactly that cell in the respective matrix, which fulfills this combination.

Ideally i simply want to code that for one matrix, than copy paste it to all other, thus i am looking for something like index(match(match)) or offset, but i don't want to be given the value but simply format the targeted cell...

Is it clear?
Click to expand...

There is a typo: "No image it is" --> "Now imagine it is.."

For clarification:

I don't look for a solution which is dependent on the sheet excel columns and rows.
I simply want to use the row and column headers of each 5x5 matrix so that i can navigate to a specific matrix element - independent of the Sheet columns and sheet rows.

Thus, I am looking for a mechanic which is kind of equal to that mechanic of the well known =offset function:

[TABLE="width: 500"]
<tbody>[TR]
[TD]START
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

With an offset function, starting in the first cell ("START") i could navigate to any cell in the matrix, fully independent of the sheet's rows or columns. It would simply start in the START cell, go x rows down and y columns to the right. x, y are simply two numbers [1;5] i can specify for each of such 5x5 matrices individually. However, offset gives me the value of that cell, but I don't want this value but simply format this cell. So for this conditional formatting, I am looking for the same kind of mechanic to target a specific cell in my matrix, but as a result I don't want to have the cell value but simply format that cell.

The described offset mechanic is independent of the sheets rows and columns since it refers to a cell by relative movements. So i could simply copy paste it to my 25 5x5 matrices, specify for each matrix the Start point and my individual x's and y's and then, the mechanic would ensure that i would have individual conditional formatting in all 25 5x5 matrices.

That's what i want to do. Is it clear now?
 
Upvote 0
Do the 5x5 matrices have header rows and columns 1,2,3,4,5 similar to below?

Are you prepared to have a CF formula for each of your 25 matrices?

Will you tailor each formula to suit? i.e. the 'Start' cell and the matrix column and row values of interest?

Excel 2007
ABCDEFGHIJKLMNOP
1
2Row3Col3Row3Col3
31234512345
410.20.40.60.80.210.40.60.80.20.41
520.150.30.450.60.1520.30.450.60.150.32
630.10.20.30.40.130.20.30.40.10.23
7400.10.150.2040.10.150.200.14
850.20.40.60.80.250.40.60.80.20.45
9Row3Col3Row3Col3
101234512345
1110.150.30.450.60.1510.30.450.60.150.31
1220.10.20.30.40.120.20.30.40.10.22
13300.10.150.2030.10.150.200.13
1440.20.40.60.80.240.40.60.80.20.44
1550.150.30.450.60.1550.30.450.60.150.35
16Row3Col3Row3Col3
Sheet1 (2)
 
Upvote 0
Snakehips said:
Do the 5x5 matrices have header rows and columns 1,2,3,4,5 similar to below?

Are you prepared to have a CF formula for each of your 25 matrices?

Will you tailor each formula to suit? i.e. the 'Start' cell and the matrix column and row values of interest?
Excel 2007
ABCDEFGHIJKLMNOP
RowColRowCol
RowColRowCol
RowColRowCol

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]0.2[/TD]
[TD="align: center"]0.4[/TD]
[TD="align: center"]0.6[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: center"]0.2[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]0.4[/TD]
[TD="align: center"]0.6[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: center"]0.2[/TD]
[TD="align: center"]0.4[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]0.15[/TD]
[TD="align: center"]0.3[/TD]
[TD="align: center"]0.45[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: center"]0.15[/TD]
[TD="align: center"][/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]0.3[/TD]
[TD="align: center"]0.45[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: center"]0.15[/TD]
[TD="align: center"]0.3[/TD]
[TD="align: center"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"]0.2[/TD]
[TD="align: center"]0.3[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"][/TD]
[TD="align: right"]3[/TD]
[TD="align: center"]0.2[/TD]
[TD="align: center"]0.3[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"]0.2[/TD]
[TD="align: center"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"]0.15[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"]0.15[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: center"]0.2[/TD]
[TD="align: center"]0.4[/TD]
[TD="align: center"]0.6[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]0.15[/TD]
[TD="align: center"]0.3[/TD]
[TD="align: center"]0.45[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]0.15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0.45[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]0.15[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"]0.2[/TD]
[TD="align: center"]0.3[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"]0.15[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0.15[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]0.2[/TD]
[TD="align: center"]0.4[/TD]
[TD="align: center"]0.6[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: center"]0.15[/TD]
[TD="align: center"]0.3[/TD]
[TD="align: center"]0.45[/TD]
[TD="align: center"]0.6[/TD]
[TD="align: center"]0.15[/TD]
[TD="align: center"][/TD]
[TD="align: right"]5[/TD]
[TD="align: center"]0.3[/TD]
[TD="align: center"]0.45[/TD]
[TD="align: center"]0.6[/TD]
[TD="align: center"]0.15[/TD]
[TD="align: center"]0.3[/TD]
[TD="align: center"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1 (2)​
Click to expand...

It's exactly how you stated it.

Are you prepared to have a CF formula for each of your 25 matrices? --> yes

Will you tailor each formula to suit? i.e. the 'Start' cell and the matrix column and row values of interest? --> yes

How can I do it then?
 
Upvote 0
As example here is one way as applied to two of the matrices.

Excel Workbook
ABCDEFGHIJKLMNO
1
2Row2Col3Row4Col1
31234512345
410.20.40.60.80.210.40.60.80.20.4
520.150.30.450.60.1520.30.450.60.150.3
630.10.20.30.40.130.20.30.40.10.2
7400.10.150.2040.10.150.200.1
850.20.40.60.80.250.40.60.80.20.4
Sheet1 (2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C41. / Formula is =CELL("address",INDEX($C$4:$G$8,$D$2,$F$2))=CELL("address",C4)Abc
J41. / Formula is =CELL("address",INDEX($J$4:$N$8,$K$2,$M$2))=CELL("address",J4)Abc




No promises but if that is what you want then I could perhaps see if I can incorporate some offset functions that will allow a single formula.
NB the 'Applies To ' range for each is just the 5x5 data range.
 
Upvote 0
Hallelujah !!

Give me 15 minutes and I will explore the offsetting.
It may work but it won't be pretty!!
 
Upvote 0
Here is example with a single CF formula.

It is relative to cell C4.
Applies To range to be C4:?? as suits your extended data.

Spacings between matrices must be as this example.
Not tested over 25x 5x5 but should be ok.
Excel Workbook
ABCDEFGHIJKLMNO
1
2Row5Col1Row4Col3
31234512345
410.20.40.60.80.210.40.60.80.20.4
520.150.30.450.60.1520.30.450.60.150.3
630.10.20.30.40.130.20.30.40.10.2
7400.10.150.2040.10.150.200.1
850.20.40.60.80.250.40.60.80.20.4
9Row3Col3Row1Col4
101234512345
1110.150.30.450.60.1510.30.450.60.150.3
1220.10.20.30.40.120.20.30.40.10.2
13300.10.150.2030.10.150.200.1
1440.20.40.60.80.240.40.60.80.20.4
1550.150.30.450.60.1550.30.450.60.150.3
16Row3Col3Row2Col2
171234512345
1810.10.20.30.40.110.20.30.40.10.2
19200.10.150.2020.10.150.200.1
2030.20.40.60.80.230.40.60.80.20.4
2140.150.30.450.60.1540.30.450.60.150.3
2250.10.20.30.40.150.20.30.40.10.2
23
Sheet1 (2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C41. / Formula is =CELL("address",INDEX(OFFSET($C$4:$G$8,(7*ROUNDDOWN(ROWS(C$1:C4)/9,0)),(7*ROUNDDOWN(COLUMNS($A4:C4)/9,0))),OFFSET($D$2,7*ROUNDDOWN(ROW(C4)/9,0),7*ROUNDDOWN(COLUMN(C4)/9,0)),OFFSET($F$2,7*ROUNDDOWN(ROW(C4)/9,0),7*ROUNDDOWN(COLUMN(C4)/9,0))))=CELL("address",C4)Abc



 
Upvote 0
Just out of interest, the 1,2,3,4,5 headers are are not used in this solution and you could do away with them if they are not otherwise necessary.
Excel Workbook
ABCDEFGHIJKLMNO
1
2
3Row5Col1Row4Col3
40.20.40.60.80.20.40.60.80.20.4
50.150.30.450.60.150.30.450.60.150.3
60.10.20.30.40.10.20.30.40.10.2
700.10.150.200.10.150.200.1
80.20.40.60.80.20.40.60.80.20.4
9
10Row3Col3Row1Col4
110.150.30.450.60.150.30.450.60.150.3
120.10.20.30.40.10.20.30.40.10.2
1300.10.150.200.10.150.200.1
140.20.40.60.80.20.40.60.80.20.4
150.150.30.450.60.150.30.450.60.150.3
16
17Row3Col3Row2Col2
180.10.20.30.40.10.20.30.40.10.2
1900.10.150.200.10.150.200.1
200.20.40.60.80.20.40.60.80.20.4
210.150.30.450.60.150.30.450.60.150.3
220.10.20.30.40.10.20.30.40.10.2
23
Sheet1 (2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C41. / Formula is =CELL("address",INDEX(OFFSET($C$4:$G$8,(7*ROUNDDOWN(ROWS(C$2:C4)/9,0)),(7*ROUNDDOWN(COLUMNS($A4:C4)/9,0))),OFFSET($D$3,7*ROUNDDOWN(ROW(C4)/9,0),7*ROUNDDOWN(COLUMN(C4)/9,0)),OFFSET($F$3,7*ROUNDDOWN(ROW(C4)/9,0),7*ROUNDDOWN(COLUMN(C4)/9,0))))=CELL("address",C4)Abc


 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Creating a random sequence of items from a weighted list with no duplicates
Replies
6
Views
366
shg
shg
N
  • Solved
Averaging every Nth cell in a column
Replies
9
Views
139
Peter_SSs
Peter_SSs
P
  • Question Question
Applying conditional formatting to merged cells
Replies
1
Views
692
Joe4
Joe4
S
  • Question Question
Conditional Formatting based on passed date
Replies
9
Views
600
SnKeaK
S
M
  • Question Question
Conditional Formatting/Moving Rows Based on Matching and Opposite Data
Replies
1
Views
293
Madam_Xavier
M

Forum statistics

Threads
1,225,134
Messages
6,183,059
Members
453,147
Latest member
Lacey D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top