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
40	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	1-2	3-4	5-6	7-8	??	1-2	3-4	5-6	7-8	??	1-2	3-4	5-6	7-8	??	1-2	3-4	5-6	7-8	??	1-2	3-4	5-6	7-8	??	1-2	3-4	5-6	7-8	??	1-2	3-4	5-6	7-8	??	1-2	3-4	5-6	7-8	??	1-2	3-4	5-6	7-8	??	1-2	3-4	5-6	7-8	??	1-2	3-4	5-6	7-8	??	1-2	3-4	5-6	7-8	??	1-2	3-4	5-6	7-8	??	1-2	3-4	5-6	7-8	??
41	1
42	2																					Mr. Excel
43		2																																																										Mr. Excel
44		1

<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>
</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 ?