How to use conditional formatting to color the row in red based on cell value in other sheet

[Ramadan2512]

I have two worksheets, sheet1 is a table to list tasks with the unit number named "task" and sheet2 is a table to list the task handling progress named "progress" . and i need to color the task row in sheet1 in red if the same task is mentioned in sheet2 and date is mentioned as done in sheet2 as you can see in screenshot

for example in the screenshot (if C2 in "sheet1"= any cell of "C" in "sheet2" and "H2" IS NOTBLANK) then color entire row in "sheet1" in red

So what is the formula that i can use in conditional formatting to do that

 

[etaf]

is the unit number UNIQUE
if it is and only appears once - then all you need is a countif() and AND - if have to match all 3 then a counitifs() for all 3 conditions and an AND

maybe an An AND with a countifs() the range will change to match your real data - but this is based on the image

countifs(task!C5:C100, progress!C15, task!D5:D100, progress!D15, task!E5:E100, progress!E15)
if you need to match all 3 elements
AND($H15<>"", countifs(task!$C$5:$C$100, progress!$C15, task!$D$5:$D$100, progress!$D15, task!$E$5:$E$100, progress!$E15)>0)
in a conditionally formatting rule

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[etaf]

=AND($H2<>"", COUNTIFS(task!$C$2:$C$24,$C2,task!$D$2:$D$24,$D2,task!$E$2:$E$24,$E2)>0)

setting up in 2 sheets as descibed - rather than the image

cond-frmt-progress task sheets-ETAF.xlsx
	A	B	C	D	E	F	G	H
1		S	UNIT	TASK	Dept	Date	Employee	Done
2		1	RT10 - 21	TASK1	UH	10/1/24	1
3		2	RWO8 - 60	TASK2	DF	10/2/24	2
4		3	RW10 - 70	TASK3	RT	10/3/24	3	2-Nov-24
Progress

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:H4	Expression	=AND($H2<>"", COUNTIFS(task!$C$2:$C$24,$C2,task!$D$2:$D$24,$D2,task!$E$2:$E$24,$E2)>0)	text	YES

cond-frmt-progress task sheets-ETAF.xlsx
	A	B	C	D	E
1		S	UNIT	TASK	Dept
2		1	RT10 - 21	TASK1	UH
3		2	RWO8 - 60	TASK2	DF
4		3	RW10 - 70	TASK3	RT
5
task

also on a share BUT only for a few days

Dropbox

www.dropbox.com

 

[Ramadan2512]

=AND($H2<>"", COUNTIFS(task!$C$2:$C$24,$C2,task!$D$2:$D$24,$D2,task!$E$2:$E$24,$E2)>0)

setting up in 2 sheets as descibed - rather than the image

cond-frmt-progress task sheets-ETAF.xlsx
	A	B	C	D	E	F	G	H
1		S	UNIT	TASK	Dept	Date	Employee	Done
2		1	RT10 - 21	TASK1	UH	10/1/24	1
3		2	RWO8 - 60	TASK2	DF	10/2/24	2
4		3	RW10 - 70	TASK3	RT	10/3/24	3	2-Nov-24
Progress

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:H4	Expression	=AND($H2<>"", COUNTIFS(task!$C$2:$C$24,$C2,task!$D$2:$D$24,$D2,task!$E$2:$E$24,$E2)>0)	text	YES

cond-frmt-progress task sheets-ETAF.xlsx
	A	B	C	D	E
1		S	UNIT	TASK	Dept
2		1	RT10 - 21	TASK1	UH
3		2	RWO8 - 60	TASK2	DF
4		3	RW10 - 70	TASK3	RT
5
task

also on a share BUT only for a few days

Dropbox

www.dropbox.com

Thank you so much for your effort and and being patient to explain - but unfortunately I'm confused and don't understand why you put "E" column in the formula..

What I need simply is two conditions (based on your shared sheets) condition #1 (if "C" in task = any cell of "C" in prgress,) condition #2 (AND the date cell "F" in progress <>"") then color the row . that's all
and as you asked before yes the unit number in C maybe repeated more than once not unique but date cell "F" can only be blank once so I depend on this cell to make the difference

thank you so much in advance

 

[etaf]

ok
so then all you need is a countif() so all you are doing is match the column c
=AND($H2<>"", COUNTIF(task!$C$2:$C$24,$C2)>0)
as the contents of D AND E wont make any difference

is it F or H ???? you are are trying to match
you example says H is not blank
now you say F

(AND the date cell "F" in progress <>"")

F is not blank in any of the rows - so how is that now matched

AS the task sheet does not have dates and none of the progress sheets F are blank
confused

=AND($H2<>"", COUNTIF(task!$C$2:$C$24,$C2)>0)

cond-frmt-progress task sheets-ETAF.xlsx
	A	B	C	D	E	F	G	H
1		S	UNIT	TASK	Dept	Date	Employee	Done
2		1	RT10 - 21	TASK1	UH	10/1/24	1
3		2	RWO8 - 60	TASK2	DF	10/2/24	2
4		3	RW10 - 70	TASK3	RT	10/3/24	3	2-Nov-24
Progress

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:H4	Expression	=AND($H2<>"", COUNTIF(task!$C$2:$C$24,$C2)>0)	text	YES

Dropbox

www.dropbox.com

 

[Ramadan2512]

Sorry I mean Date Cell "H" not "H" which is done

 

[etaf]

Sorry I mean Date Cell "H" not "H" which is done

ok i assume you mean H NOT F
and i have shown the solution just matching on column C , the UNIT

BUT basically the task code just needs to exist in the TASK table as thats all you are really checking , does TASK exist

SO if it did have a date in H - BUT was not on the task list - it would not colour

 

[Ramadan2512]

I'm trying to apply the formula in conditional formating but I get a message that (I cannot use reference to other worksheet in conditional formating
sorry if i didn't mention that the tow sheets are not in the same workbook but different workbooks I though may not effect
and also in your last formula =AND($H2<>"", COUNTIF(task!$C$2:$C$24,$C2)>0) there is not mention to prgress worksheet ???

 

[etaf]

so no need to mention progress sheet as the conditional formatting is in that sheet
so
$H2
could be
'progress'!$H2
and
$C2
could be
'progress'!$C2
But no need as the conditional formatting is in the progress sheet

AND(progress!$H2<>"", COUNTIF(task!$C$2:$C$24,progress!$C2)>0)

other worksheet
OR a different workbook ????
confused now

Then maybe more complicated as both workbooks would need to also be OPEN , if the TASK workbook is closed - then need to use a formula that will work with closed workbooks - and need the full path of where the workbook is , if in a different folder
need to look into that
BUT meanwhile
Explain where is the TASK workbook going to exist - on a PC, same folder , different folder , a share , ONLINE , sharepoint etc

 

[Ramadan2512]

so no need to mention progress sheet as the conditional formatting is in that sheet
so
$H2
could be
'progress'!$H2
and
$C2
could be
'progress'!$C2
But no need as the conditional formatting is in the progress sheet

AND(progress!$H2<>"", COUNTIF(task!$C$2:$C$24,progress!$C2)>0)

other worksheet
OR a different workbook ????
confused now

Then maybe more complicated as both workbooks would need to also be OPEN , if the TASK workbook is closed - then need to use a formula that will work with closed workbooks - and need the full path of where the workbook is , if in a different folder
need to look into that
BUT meanwhile
Explain where is the TASK workbook going to exist - on a PC, same folder , different folder , a share , ONLINE , sharepoint etc

both workbooks are on my pc desktop
here I share both of my files with you in this link ( Download Data package from November 26th.)