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?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
rippchen10247,

Do you mean like.....
Excel Workbook
ABCDEFG
10.20.40.60.8RowCol
20.150.30.450.634
30.10.20.30.4
400.10.150.2
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A11. / Formula is =AND(ROW(A1)=$F$2,COLUMN(A1)=$G$2)Abc



Hope that helps
 
Upvote 0
Kind of...
However, it does not work for me as you stated above. :(

Referring to your example: I want to have a conditional formatting for the A1:D4 field, but only for the single element which is the intersection determined by cells F2 and G2.
 
Last edited:
Upvote 0
[TABLE="width: 1283"]
<tbody>[TR]
[TD]
[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1 [/TD]
[TD]0.200[/TD]
[TD]0.400[/TD]
[TD]0.600[/TD]
[TD]0.800[/TD]
[TD]1.000[/TD]
[TD][/TD]
[TD][/TD]
[TD]1 [/TD]
[TD]0.200[/TD]
[TD]0.400[/TD]
[TD]0.600[/TD]
[TD]0.800[/TD]
[TD]1.000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1 [/TD]
[TD]0.200[/TD]
[TD]0.400[/TD]
[TD]0.600[/TD]
[TD]0.800[/TD]
[TD]1.000[/TD]
[TD][/TD]
[TD][/TD]
[TD]1 [/TD]
[TD]0.200[/TD]
[TD]0.400[/TD]
[TD]0.600[/TD]
[TD]0.800[/TD]
[TD]1.000[/TD]
[/TR]
[TR]
[TD]2 [/TD]
[TD]0.150[/TD]
[TD]0.300[/TD]
[TD]0.450[/TD]
[TD]0.600[/TD]
[TD]0.750[/TD]
[TD][/TD]
[TD]2 [/TD]
[TD]0.150[/TD]
[TD]0.300[/TD]
[TD]0.450[/TD]
[TD]0.600[/TD]
[TD]0.750[/TD]
[TD][/TD]
[TD][/TD]
[TD]2 [/TD]
[TD]0.150[/TD]
[TD]0.300[/TD]
[TD]0.450[/TD]
[TD]0.600[/TD]
[TD]0.750[/TD]
[TD][/TD]
[TD]2 [/TD]
[TD]0.150[/TD]
[TD]0.300[/TD]
[TD]0.450[/TD]
[TD]0.600[/TD]
[TD]0.750[/TD]
[/TR]
[TR]
[TD]3 [/TD]
[TD]0.100[/TD]
[TD]0.200[/TD]
[TD]0.300[/TD]
[TD]0.400[/TD]
[TD]0.500[/TD]
[TD][/TD]
[TD]3 [/TD]
[TD]0.100[/TD]
[TD]0.200[/TD]
[TD]0.300[/TD]
[TD]0.400[/TD]
[TD]0.500[/TD]
[TD][/TD]
[TD][/TD]
[TD]3 [/TD]
[TD]0.100[/TD]
[TD]0.200[/TD]
[TD]0.300[/TD]
[TD]0.400[/TD]
[TD]0.500[/TD]
[TD][/TD]
[TD]3 [/TD]
[TD]0.100[/TD]
[TD]0.200[/TD]
[TD]0.300[/TD]
[TD]0.400[/TD]
[TD]0.500[/TD]
[/TR]
[TR]
[TD]4 [/TD]
[TD]0.050[/TD]
[TD]0.100[/TD]
[TD]0.150[/TD]
[TD]0.200[/TD]
[TD]0.250[/TD]
[TD][/TD]
[TD]4 [/TD]
[TD]0.050[/TD]
[TD]0.100[/TD]
[TD]0.150[/TD]
[TD]0.200[/TD]
[TD]0.250[/TD]
[TD][/TD]
[TD][/TD]
[TD]4 [/TD]
[TD]0.050[/TD]
[TD]0.100[/TD]
[TD]0.150[/TD]
[TD]0.200[/TD]
[TD]0.250[/TD]
[TD][/TD]
[TD]4 [/TD]
[TD]0.050[/TD]
[TD]0.100[/TD]
[TD]0.150[/TD]
[TD]0.200[/TD]
[TD]0.250[/TD]
[/TR]
[TR]
[TD]5 [/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD][/TD]
[TD]5 [/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD][/TD]
[TD][/TD]
[TD]5 [/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD][/TD]
[TD]5 [/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 1283"]
<tbody>[TR]
[TD]
[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1 [/TD]
[TD]0.200[/TD]
[TD]0.400[/TD]
[TD]0.600[/TD]
[TD]0.800[/TD]
[TD]1.000[/TD]
[TD][/TD]
[TD][/TD]
[TD]1 [/TD]
[TD]0.200[/TD]
[TD]0.400[/TD]
[TD]0.600[/TD]
[TD]0.800[/TD]
[TD]1.000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1 [/TD]
[TD]0.200[/TD]
[TD]0.400[/TD]
[TD]0.600[/TD]
[TD]0.800[/TD]
[TD]1.000[/TD]
[TD][/TD]
[TD][/TD]
[TD]1 [/TD]
[TD]0.200[/TD]
[TD]0.400[/TD]
[TD]0.600[/TD]
[TD]0.800[/TD]
[TD]1.000[/TD]
[/TR]
[TR]
[TD]2 [/TD]
[TD]0.150[/TD]
[TD]0.300[/TD]
[TD]0.450[/TD]
[TD]0.600[/TD]
[TD]0.750[/TD]
[TD][/TD]
[TD]2 [/TD]
[TD]0.150[/TD]
[TD]0.300[/TD]
[TD]0.450[/TD]
[TD]0.600[/TD]
[TD]0.750[/TD]
[TD][/TD]
[TD][/TD]
[TD]2 [/TD]
[TD]0.150[/TD]
[TD]0.300[/TD]
[TD]0.450[/TD]
[TD]0.600[/TD]
[TD]0.750[/TD]
[TD][/TD]
[TD]2 [/TD]
[TD]0.150[/TD]
[TD]0.300[/TD]
[TD]0.450[/TD]
[TD]0.600[/TD]
[TD]0.750[/TD]
[/TR]
[TR]
[TD]3 [/TD]
[TD]0.100[/TD]
[TD]0.200[/TD]
[TD]0.300[/TD]
[TD]0.400[/TD]
[TD]0.500[/TD]
[TD][/TD]
[TD]3 [/TD]
[TD]0.100[/TD]
[TD]0.200[/TD]
[TD]0.300[/TD]
[TD]0.400[/TD]
[TD]0.500[/TD]
[TD][/TD]
[TD][/TD]
[TD]3 [/TD]
[TD]0.100[/TD]
[TD]0.200[/TD]
[TD]0.300[/TD]
[TD]0.400[/TD]
[TD]0.500[/TD]
[TD][/TD]
[TD]3 [/TD]
[TD]0.100[/TD]
[TD]0.200[/TD]
[TD]0.300[/TD]
[TD]0.400[/TD]
[TD]0.500[/TD]
[/TR]
[TR]
[TD]4 [/TD]
[TD]0.050[/TD]
[TD]0.100[/TD]
[TD]0.150[/TD]
[TD]0.200[/TD]
[TD]0.250[/TD]
[TD][/TD]
[TD]4 [/TD]
[TD]0.050[/TD]
[TD]0.100[/TD]
[TD]0.150[/TD]
[TD]0.200[/TD]
[TD]0.250[/TD]
[TD][/TD]
[TD][/TD]
[TD]4 [/TD]
[TD]0.050[/TD]
[TD]0.100[/TD]
[TD]0.150[/TD]
[TD]0.200[/TD]
[TD]0.250[/TD]
[TD][/TD]
[TD]4 [/TD]
[TD]0.050[/TD]
[TD]0.100[/TD]
[TD]0.150[/TD]
[TD]0.200[/TD]
[TD]0.250[/TD]
[/TR]
[TR]
[TD]5 [/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD][/TD]
[TD]5 [/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD][/TD]
[TD][/TD]
[TD]5 [/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD][/TD]
[TD]5 [/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[TD]0.000[/TD]
[/TR]
</tbody>[/TABLE]


--> maybe for clarification: I cannot use the rows and columns of my sheet, as I will have multiple tables side by side. I want to have conditional formatting for each table. in each table, there will be a different single element highlighted, based on the specific row, column combination as indicated.
 
Upvote 0
Hello
this is the formula to paste in CF formula box

=AND($A2=$H$2;B$1=$H$4)


Book1
ABCDEFGH
112345Row Headers #
210,2000,4000,6000,8001,0003
320,1500,3000,4500,6000,750Column headers #
430,1000,2000,3000,4000,5002
540,0500,1000,1500,2000,250
650,0000,0000,0000,0000,000
Foglio2
 
Upvote 0
See, i think this will work, however, it gets super complicated if I have about 25 5x5 tables side by side, which form 5x5 tables of 5x5 matrices themselves.

In this case, I have to do this manually for every 5x5. Isn't there any solution which simply uses the column and row numberings (1-5) which are equal for all tables? Could save a lot of time!
 
Last edited:
Upvote 0
Hello I'm glad this formula work
I belive (as I know) you need to do CF for every single table but is not so complicated

1st copy the formula

2nd select the range (NOT the Row and columns headers)

3rd ALT+O+D and paste the formula in box and give format

you must do it 25 times

thank anyway for your feedback
 
Upvote 0
hm.. still not satisfactory. I do have to do this already 25 times for one matrix don't I? Means 25x25 for 25 5x5 side by side... Still way to time consuming...
 
Upvote 0
You've changed the goalposts somewhat with 'a 5x5 matrix table' becoming 25 x 5 x 5.

What are your actual cell references for......

The specified row
The specified column
The top left cell of the top left 5x5 matrix

What are the row and column spacing between adjacent matrices?

If necessary are you able to amend the spacings?
 
Upvote 0
Snakehips said:
You've changed the goalposts somewhat with 'a 5x5 matrix table' becoming 25 x 5 x 5.

What are your actual cell references for......

The specified row
The specified column
The top left cell of the top left 5x5 matrix

What are the row and column spacing between adjacent matrices?

If necessary are you able to amend the spacings?
Click to expand...

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?
 
Last edited:
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
358
shg
shg
N
  • Solved
Averaging every Nth cell in a column
Replies
9
Views
138
Peter_SSs
Peter_SSs
P
  • Question Question
Applying conditional formatting to merged cells
Replies
1
Views
691
Joe4
Joe4
S
  • Question Question
Conditional Formatting based on passed date
Replies
9
Views
590
SnKeaK
S
M
  • Question Question
Conditional Formatting/Moving Rows Based on Matching and Opposite Data
Replies
1
Views
292
Madam_Xavier
M

Forum statistics

Threads
1,225,038
Messages
6,182,522
Members
453,124
Latest member
reshmawils

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