One Conditional Format to Check Different Ranges Depending on the Position of the Root Cell

[Michael Kensy]

first one thing for the admins. I always have the problem that I am obviously logged out while I write my post. That means I always lose everything and must be smarter next time to first copy my post before I try to post, so that I can paste after I am forced to login again.

Anyway

I am trying to implement ONE CF which investigates Area1 if it operates in column L or Area2 if it operates in column M

Semester 01-2017

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

BB

BC

BD

BE

BF

BG

BH

BI

BJ

BK

BL

BM

BN

BO

BP

BQ

BR

BS

BT

BU

BV

BW

BX

BY

BZ

CA

CB

CC

CD

CE

CF

CG

CH

CI

CJ

CK

CL

CM

CN

CO

CP

CQ

CR

CS

CT

CU

CV

CW

CX

CY

CZ

??

??

??

??

??

??

??

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:59px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:55px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:55px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:55px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:55px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:55px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:55px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:55px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:55px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:55px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:55px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:55px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:55px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:55px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:55px;"><col style="width:41px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]40[/TD]
[TD="align: center"]01[/TD]
[TD="align: center"]02[/TD]
[TD="align: center"]03[/TD]
[TD="align: center"]04[/TD]
[TD="align: center"]05[/TD]
[TD="align: center"]06[/TD]
[TD="align: center"]07[/TD]
[TD="align: center"]08[/TD]
[TD="align: center"]09[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]1-2[/TD]
[TD="align: center"]3-4[/TD]
[TD="align: center"]5-6[/TD]
[TD="align: center"]7-8[/TD]
[TD="align: center"]??[/TD]
[TD="align: center"]1-2[/TD]
[TD="align: center"]3-4[/TD]
[TD="align: center"]5-6[/TD]
[TD="align: center"]7-8[/TD]
[TD="align: center"]??[/TD]
[TD="align: center"]1-2[/TD]
[TD="align: center"]3-4[/TD]
[TD="align: center"]5-6[/TD]
[TD="align: center"]7-8[/TD]
[TD="align: center"]??[/TD]
[TD="align: center"]1-2[/TD]
[TD="align: center"]3-4[/TD]
[TD="align: center"]5-6[/TD]
[TD="align: center"]7-8[/TD]
[TD="align: center"]??[/TD]
[TD="align: center"]1-2[/TD]
[TD="align: center"]3-4[/TD]
[TD="align: center"]5-6[/TD]
[TD="align: center"]7-8[/TD]
[TD="align: center"]??[/TD]
[TD="align: center"]1-2[/TD]
[TD="align: center"]3-4[/TD]
[TD="align: center"]5-6[/TD]
[TD="align: center"]7-8[/TD]
[TD="align: center"]??[/TD]
[TD="align: center"]1-2[/TD]
[TD="align: center"]3-4[/TD]
[TD="align: center"]5-6[/TD]
[TD="align: center"]7-8[/TD]
[TD="align: center"]??[/TD]
[TD="align: right"]1-2[/TD]
[TD="align: right"]3-4[/TD]
[TD="align: right"]5-6[/TD]
[TD="align: right"]7-8[/TD]

[TD="align: right"]1-2[/TD]
[TD="align: right"]3-4[/TD]
[TD="align: right"]5-6[/TD]
[TD="align: right"]7-8[/TD]

[TD="align: right"]1-2[/TD]
[TD="align: right"]3-4[/TD]
[TD="align: right"]5-6[/TD]
[TD="align: right"]7-8[/TD]

[TD="align: right"]1-2[/TD]
[TD="align: right"]3-4[/TD]
[TD="align: right"]5-6[/TD]
[TD="align: right"]7-8[/TD]

[TD="align: right"]1-2[/TD]
[TD="align: right"]3-4[/TD]
[TD="align: right"]5-6[/TD]
[TD="align: right"]7-8[/TD]

[TD="align: right"]1-2[/TD]
[TD="align: right"]3-4[/TD]
[TD="align: right"]5-6[/TD]
[TD="align: right"]7-8[/TD]

[TD="align: right"]1-2[/TD]
[TD="align: right"]3-4[/TD]
[TD="align: right"]5-6[/TD]
[TD="align: right"]7-8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]41[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=a5a5a5]#a5a5a5[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=a5a5a5]#a5a5a5[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]42[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c2d69a]#c2d69a[/URL] , align: center"]Mr. Excel[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fde9d9]#fde9d9[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=a5a5a5]#a5a5a5[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]43[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=a5a5a5]#a5a5a5[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c2d69a]#c2d69a[/URL] , align: center"]Mr. Excel[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fde9d9]#fde9d9[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]44[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=a5a5a5]#a5a5a5[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d8d8d8]#d8d8d8[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=a5a5a5]#a5a5a5[/URL] "] [/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


More concrete, I want that CF to deliver TRUE if L41>1 and AG41:BO41 is not empty. That one I can do and it also works for below rows. The thing I dont know is how can the same CF applied to M41 check if M41>1 and $BQ41:CZ41 is not empty? Please notice also that column BP can not be checked if blank as it always contains a value.
--
Regards Michael

 

[Special-K99]

These should do it.
Idientify what column you're looking at and adjust the range as necessary

OR(AND(COLUMN()=12,COUNTA(AG41:BO41)=0),AND(COLUMN()=13,COUNTA(BQ41:CZ41)=0))

 

[Michael Kensy]

Hola Special-K99

I guess that skips the first part I want to be checked. The first part is if the value in that column is >1.

I created a new example which is hopefully more clear:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Weeks Status			Week 1					Week 2					Week 3
2	1	2	3	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday
3	1			Something in here does not matter as A3
4		1							Something in here does not matter as B4
5	2			No alarm in column A needed as column A>1
6		3
7			4											No alarm in column C needed as column C=4
Sheet1

So, I want B6 to turn yellow because the range I6:M6 is blank.
--
Regards Michael

 

[Michael Kensy]

I guess I got you now. I just have to create a third condition.

 

[Special-K99]

I dont know where we are now as you've changed the column references from your initial example.
What's the current formula ?