IF and MATCH

[PaulyK]

Hi,

I have two spreadsheets. Non Compliance and Time Allocation Days. I have an IF formula which displays "Entered" if a cell in the Non-Compliance spreadsheet has data entered and "Not Entered" if the cell is blank each month column..
In Column A on both spreadsheets I have a list of Names. Columns D,F,H,J contain a separate unrelated Formula on my Master doc, so have left them blank in this ecample.

What i need to do is Match the name and the IF statement. i.e Did Person 3 Enter Anything in the May column? Is there a way to do this? Currently as a fix I am having to copy the current list of People in Column A of the 'Time Allocation - Days' sheet, in column A of the 'Non-Compliance' spreadsheet - but that is not ideal!

Or am i doing it all wrong?

Paul

Time-Allocation Days

Book4
	A	B	C	D	E	F	G	H	I	J
1	NAME	TEAM	Days for Apr-22	Apr-22	Days for May 22	May-22	Days for June 22	Jun-22	Days for July 22	Jul-22
2	Person1		15.0		15.0		15.0		10.0
3	Person2								1.0
4	Person3		1.0		1.0		1.0		1.0
5	Person4				1.0
6	Person5
7	Person6		20.0		19.0		14.0
8	Person7
9	Person8		19.0		20.0		20.0		20.0
10	Person9
11	Person10		20.0		20.0
12	Person11		11.0
13	Person12		7.5		1.0
14	Person13		3.0
15	Person14		21.0		4.0		4.0		4.0
16	Person15		5.0
17	Person16		14.0		11.0
18	Person17		20.0
19	Person18								6.0
20	Person19		4.0
21	Person20				5.0		5.0
22	Person21
23	Person22		22.0		22.0		22.0		22.0
24	Person23
25	Person24				7.5				21.0
Time allocation - Days

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:J25	Expression	=ISBLANK(C2)=TRUE	text	NO

Non-Compliance

Book4
	A	B	C	D	E	F
3	Blanks / No Entry		April	May	June	July
4	Name	Team	No Entry April	No Entry May	No Entry June	No Entry July
5	Person1		Entered	Entered	Entered	Entered
6	Person2		No Entry	No Entry	No Entry	No Entry
7	Person3		No Entry	No Entry	No Entry	No Entry
8	Person4		No Entry	No Entry	No Entry	No Entry
9	Person5		No Entry	No Entry	No Entry	No Entry
10	Person6		Entered	Entered	Entered	No Entry
11	Person7		No Entry	No Entry	No Entry	No Entry
12	Person8		Entered	Entered	Entered	Entered
13	Person9		No Entry	No Entry	No Entry	No Entry
14	Person10		Entered	Entered	No Entry	No Entry
15	Person11		No Entry	No Entry	No Entry	No Entry
16	Person12		No Entry	No Entry	No Entry	No Entry
17	Person13		No Entry	No Entry	No Entry	No Entry
18	Person14		No Entry	Entered	Entered	Entered
19	Person15		Entered	No Entry	No Entry	No Entry
20	Person16		Entered	No Entry	No Entry	No Entry
21	Person17		Entered	No Entry	No Entry	No Entry
22	Person18		No Entry	No Entry	No Entry	No Entry
23	Person19		No Entry	No Entry	No Entry	No Entry
24	Person20		No Entry	Entered	Entered	No Entry
25	Person21		No Entry	No Entry	No Entry	No Entry
26	Person22		Entered	Entered	Entered	Entered
27	Person23		No Entry	No Entry	No Entry	No Entry
28	Person24		No Entry	No Entry	No Entry	No Entry
Non-Compliance

Cell Formulas
Range	Formula
C5:C28	C5	=IF('[Resource Allocation Tool - RAT.xlsx]Time allocation - Days'!I3<>"","Entered","No Entry")
D5:D28	D5	=IF('[Resource Allocation Tool - RAT.xlsx]Time allocation - Days'!K3<>"","Entered","No Entry")
E5:E28	E5	=IF('[Resource Allocation Tool - RAT.xlsx]Time allocation - Days'!M3<>"","Entered","No Entry")
F5:F28	F5	=IF('[Resource Allocation Tool - RAT.xlsx]Time allocation - Days'!O3<>"","Entered","No Entry")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5:F28	Cell Value	="No Entry"	text	NO
C5:F28	Cell Value	="Entered"	text	NO

 

[Fluff]

Try

=IF(countifs('[Resource Allocation Tool - RAT.xlsx]Time allocation - Days'!A:A,A5,'[Resource Allocation Tool - RAT.xlsx]Time allocation - Days'!I:I<>""),"Entered","No Entry")

 

[PaulyK]

Realise i didn't update the example table formula in my example.

Non Compliance Example.xlsm
	A	B	C	D	E	F
3	Blanks / No Entry		April	May	June	July
4	Name	Team	No Entry April	No Entry May	No Entry June	No Entry July
5	Person1		Entered	Entered	Entered	Entered
6	Person2		No Entry	No Entry	No Entry	Entered
7	Person3		Entered	Entered	Entered	Entered
8	Person4		No Entry	Entered	No Entry	No Entry
9	Person5		No Entry	No Entry	No Entry	No Entry
10	Person6		Entered	Entered	Entered	No Entry
11	Person7		No Entry	No Entry	No Entry	No Entry
12	Person8		Entered	Entered	Entered	Entered
13	Person9		No Entry	No Entry	No Entry	No Entry
14	Person10		Entered	Entered	No Entry	No Entry
15	Person11		Entered	No Entry	No Entry	No Entry
16	Person12		Entered	Entered	No Entry	No Entry
17	Person13		Entered	No Entry	No Entry	No Entry
18	Person14		Entered	Entered	Entered	Entered
19	Person15		Entered	No Entry	No Entry	No Entry
20	Person16		Entered	Entered	No Entry	No Entry
21	Person17		Entered	No Entry	No Entry	No Entry
22	Person18		No Entry	No Entry	No Entry	Entered
23	Person19		Entered	No Entry	No Entry	No Entry
24	Person20		No Entry	Entered	Entered	No Entry
25	Person21		No Entry	No Entry	No Entry	No Entry
26	Person22		Entered	Entered	Entered	Entered
27	Person23		No Entry	No Entry	No Entry	No Entry
28	Person24		No Entry	Entered	No Entry	Entered
Non-Compliance

Cell Formulas
Range	Formula
C5:C28	C5	=IF('Time allocation - Days'!C2<>"","Entered","No Entry")
D5:D28	D5	=IF('Time allocation - Days'!E2<>"","Entered","No Entry")
E5:E28	E5	=IF('Time allocation - Days'!G2<>"","Entered","No Entry")
F5:F28	F5	=IF('Time allocation - Days'!I2<>"","Entered","No Entry")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5:F28	Cell Value	="No Entry"	text	NO
C5:F28	Cell Value	="Entered"	text	NO

 

[PaulyK]

Try

=IF(countifs('[Resource Allocation Tool - RAT.xlsx]Time allocation - Days'!A:A,A5,'[Resource Allocation Tool - RAT.xlsx]Time allocation - Days'!I:I<>""),"Entered","No Entry")

oh i like the look of that... however I am getting an error message to say "You've entered too few arguments for this function"

 

[Fluff]

Oops it should be

=IF(countifs('[Resource Allocation Tool - RAT.xlsx]Time allocation - Days'!A:A,A5,'[Resource Allocation Tool - RAT.xlsx]Time allocation - Days'!I:I,"<>"),"Entered","No Entry")

 

[PaulyK]

Oops it should be

=IF(countifs('[Resource Allocation Tool - RAT.xlsx]Time allocation - Days'!A:A,A5,'[Resource Allocation Tool - RAT.xlsx]Time allocation - Days'!I:I,"<>"),"Entered","No Entry")

Brilliant. Thank you

 

[Fluff]

You're welcome & thanks for the feedback.