SOLVED

HOW TO HIGHLIGHT THE KM LOCATION IN A SECTION IF IT IS IN SANCTIONED LOCATION LIST?

Copper Contributor

I am working on highlighting a cell with some conditions below

cdmtrackgnt_0-1660198859343.png

cdmtrackgnt_2-1660199076022.png

 

These are two data sets I have. the second one is sanctioned km list for works.

Now I want to highlight cell in column J in first sheet if it is in between cells T & U in second sheet with in same section and line (i.e., Column C&E in sheet 1 should match with Column J & L in sheet2).

 

I have been trying using if conditions, vlookups, index functions. but could not highlight the cells. Can anyone help me on this?

 

2 Replies
best response confirmed by cdmtrackgnt (Copper Contributor)
Solution

@cdmtrackgnt 

=MATCH(1,(J287>=Tabelle2!$T$406:$T$424)*(J287<=Tabelle2!$U$406:$U$424)*(C287=Tabelle2!$J$406:$J$424)*(E287=Tabelle2!$L$406:$L$424),0)

You can try this rule for conditional formatting which works in my sheet. The sheet names "Tabelle1" and "Tabelle2" have to be replaced with the names of the actual sheets.

cf from to.JPGcf.JPG

1 best response

Accepted Solutions
best response confirmed by cdmtrackgnt (Copper Contributor)
Solution

@cdmtrackgnt 

=MATCH(1,(J287>=Tabelle2!$T$406:$T$424)*(J287<=Tabelle2!$U$406:$U$424)*(C287=Tabelle2!$J$406:$J$424)*(E287=Tabelle2!$L$406:$L$424),0)

You can try this rule for conditional formatting which works in my sheet. The sheet names "Tabelle1" and "Tabelle2" have to be replaced with the names of the actual sheets.

cf from to.JPGcf.JPG

View solution in original post