Conditional Formatting based on text value in cell above

[NWPhotoExplorer]

I'm working on a sheet that I use to keep track of my time worked and make sure it matches my paycheck. I have a few fields that have different headers, based on the type of payment received. I am trying to figure out how to write a conditional formatting formula that will look at the header title and then go to a specific field and see how many hours were there and match it to what was entered of the paycheck. It will color code it RED if I was shorted and GREEN if it matches.

As I am new to excel, I am still learning which functions to use and when, so I am not really sure what I should use here. Basically, I want to say if header Z1 = ABC PAY, look in J2 and if the value in Z2 matches J2, color it green, if not red. But I don't know how to go about setting that up in a conditional format. I am hoping someone here can help me get this to work correctly.

Thanks!

 

[Toadstool]

Hi NWPhotoExplorer,

Is this what you wanted?

NWPhotoExplorer.xlsx
	J	K														Y	Z
1																	ABC PAY
2	25																25
3	25																22
4	33																33
5	33																44
6
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Z2:Z12	Expression	=AND($Z$1="ABC PAY",J2<>Z2,J2<>"")	text	NO
Z2:Z12	Expression	=AND($Z$1="ABC PAY",Z2=J2,Z2<>"")	text	NO

 

[NWPhotoExplorer]

@

Hi NWPhotoExplorer,

Is this what you wanted?

NWPhotoExplorer.xlsx
	J	K														Y	Z
1																	ABC PAY
2	25																25
3	25																22
4	33																33
5	33																44
6
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Z2:Z12	Expression	=AND($Z$1="ABC PAY",J2<>Z2,J2<>"")	text	NO
Z2:Z12	Expression	=AND($Z$1="ABC PAY",Z2=J2,Z2<>"")	text	NO

That is pretty close to what I'm after. How do I set it up to look for just ABC or Abc. That way incase I don't have the labels right, the conditional formatting works based on abc and is not case dependent.

 

[Toadstool]

The check isn't case dependent.

NWPhotoExplorer.xlsx
	J	K														Y	Z
1																	aBc pAy
2	25																25
3	25																22
4	33																33
5	33																44
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Z2:Z12	Expression	=AND($Z$1="aBC PAY",J2<>Z2,J2<>"")	text	NO
Z2:Z12	Expression	=AND($Z$1="ABC pay",Z2=J2,Z2<>"")	text	NO

 

[NWPhotoExplorer]

Is there a way to do it so it only looks for "abc" and not ABC PAY? That way in case there is an extra space or something that someone else does, it just has to have abc or xyz or whatever else I set up in the search parameter.

 

[Toadstool]

Is there a way to do it so it only looks for "abc" and not ABC PAY? That way in case there is an extra space or something that someone else does, it just has to have abc or xyz or whatever else I set up in the search parameter.

Hold on! Are you saying it may not be in column Z? If so then what column range should be searched?

If it's the case you enter a name and that must be somewhere in Z1 then try this

NWPhotoExplorer.xlsx
	J	K														Y	Z	AA	AB
1																	JIM Pay		Name
2	25																25		Jim
3	25																22
4	33																33
5	33																44
6
Sheet1 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Z2:Z12	Expression	=AND(ISNUMBER(SEARCH($AB$2,$Z$1)),J2<>Z2,J2<>"")	text	NO
Z2:Z12	Expression	=AND(ISNUMBER(SEARCH($AB$2,$Z$1)),J2=Z2,J2<>"")	text	NO

 

[NWPhotoExplorer]

The field will stay in column Z.

Here is a rough version of what I am working on.

Work Hours.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1		Week	Start Date	End Date	Holiday Hours		PTO Hours	Regular Hours		Jury Duty	FMLA Hours		Overtime	Total Hours	PTO Total	PTO Accrual		Check Date	PTO Accrued	PTO Balance							Other Pay		Total Hours
Sheet1

Basically I am wanting to be able to type in Regular Pay and it looks in H and grabs the value. Or if I type in Jury Duty Pay, it finds Jury Duty in column J.

 

[Toadstool]

I'm not seeing how that relates to setting of a Conditional Format and there would be no match of Regular Pay or Jury Duty Pay as neither text is in the headings, although Regular and Jury both are.

This lets you type something in A1 and searches for a partial match in the headings and returns the same row value under that heading.

NWPhotoExplorer.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Regular	Week	Start Date	End Date	Holiday Hours		PTO Hours	Regular Hours		Jury Duty	FMLA Hours		Overtime	Total Hours	PTO Total	PTO Accrual		Check Date	PTO Accrued	PTO Balance							Other Pay		Total Hours
2	7	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28
Sheet2

Cell Formulas
Range	Formula
A2	A2	=INDEX($B$1:$AC$9999,ROW(),MATCH("*"&A1&"*",$B$1:$AC$1,0))