CODEWITHSUNDEEP
excelvba
Peter
Peter

Reputation: 65

Visualizing combinations in an Excel worksheet

I have a worksheet that detects toxic combinations (two variables that are not supposed to exist together). In one sheet I want to cross all the variables there are and highlight the cell that crosses the two variables that are toxic.

for example cell E1 (variable 1) and A5 (variable 2) are toxic combinations, so i want to highlight cell E5 (this can be with a X or a formatting conditions, it doesn't matter).

On another sheet these toxic combinations are defined, simply by entering the two variables behind each other on the same row.

I'm having trouble understanding how to approach this. one thought is to enter a formula (if such exists) that goes something like the following:

=IF cell E1 and cell A5 are on the same row in worksheet ... then x else ""

So my question is: does anyone have experience with this situation, if so, how would/ did you tackle this. Or is there a formula that I can use demonstrated above? Or is it wise to use VBA?

Please let me know if anything is unclear.

Edit: screenshots

The worksheet where the toxic combinations are determined: enter image description here

The worksheet where the toxic combinations are 'visible'

enter image description here

Upvotes: 1

Views: 231

Answers (1)

Peter M.
Peter M.

Reputation: 124

you can use vlookup for this. Suppose in your SOD matrix, the element names are in column A and in row 1, and that in your SOD description the elements are in column C and E. And suppose that your SOD description is on a tab called "SODdesc"

the formula in cell C5 would be

=if(VLOOKUP($A5;SODdesc!C:E;3;0)=C$1;"TOXIC";"")

This only works if there are no duplicates in column C of the SOD description sheet.

Another possible solution that works also if there are duplicates, is to create an extra column (G) where for example G10 would be =C10&E10 and a column H, with H10 is =E10&C10

then the formula in C5 would become: =if(not(isna(vlookup($A5&C$1;SODdesc!G:G;1;0)));if(not(isna(vlookup($A5&C$1;SODdesc!H:H;1;0)));"TOXIC";""))

I didn't create a whole file to test the formula's, but if you know a little bit how excel formulas work, you should get the idea.

Upvotes: 1

Related Questions