multiple cell len formula

[Linsie]

Hi,


I have a formula of =IF(LEN(C2)=0, "ACTION REQUIRED", "Completed") which is working great when only looking at one cell (ie C2).

However, in some columns I need it to work to search cells H2:K2 also.

I have tried using =IF(LEN(H2:K2)=0, "ACTION REQUIRED", "Completed") but it returns a #spill! error.

How can I resolve this please?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you explain what you are trying to do & what the result should be.

 

[Linsie]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you explain what you are trying to do & what the result should be.

Thanks Fluff, was not aware of that part in my profile, I have just updated it.

I am using office 2021.


I have 2 tables the orange one is fed data that shows me the date the course was completed.
The yellow table is used to notify people of actions required, however, some courses there are a few different versions, so I need the formula to check the cells for each variation of the name and return the overall status.

(hope this all makes sense)

Training Tracker 2024 v2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Engineer	Team	A Guide to Data Classification using CPI-810 (VZ47959)	Anti-Bribery and Anti-Corruption (VZ151651)	CPNI Annual Training 2023 - Employees	CPNI Annual Training 2024 (VZ152201)	Global Trade Compliance	Phishing - Don't Get Hooked!	Phishing - Don't Get Hooked! (Cyber Health at Verizon Plan - Intermediate)	Phishing Awareness: Don't Get Hooked	Phishing Awareness: Don't Get Hooked (VZ103227)	Privacy and Information Security (VZ108040)	Privacy and Information Security: Online Course	Privacy and Information Security: Online Course (VZ108040)	Privileged Users 101	Privileged Users 101 (VZ130145)	Privileged Users 101 (VZ130145A)	Reliability Matters: Episode 6 Maintenance Hygiene (VZ132307)
2	Enginner 1	ODCM	15/09/2023	16/11/2023	30/03/2023	08/02/2024	########				20/06/2023			22/06/2023
3	Enginner 2	GNO-A		14/11/2023	17/04/2023	09/02/2024	########
4	Enginner 3	GNO-A		24/11/2023	22/03/2023		########				10/01/2024
5	Enginner 4	OSES		30/11/2023	20/04/2023	23/02/2024	########		15/02/2023	15/02/2023					16/02/2023
6	Enginner 5	ODCM	15/09/2023	15/11/2023	17/03/2023	08/02/2024	########	13/06/2023	13/06/2023	01/02/2023				13/06/2023
7	Enginner 6	Storage		24/11/2023	17/04/2023	04/04/2024	########
8	Enginner 7	ODCM	19/09/2023	28/11/2023	30/03/2023	13/03/2024	########				04/08/2023	10/08/2023				10/08/2023
9	Enginner 8	ODCM	00/01/1900	09/11/2023	21/03/2023	02/04/2024	########			01/02/2023
10	Enginner 9	PSD		16/11/2023	17/03/2023	07/03/2024	########			23/02/2023
11	Enginner 10	OSES		22/11/2023	28/03/2023	14/03/2024	########		19/07/2023		21/07/2023
12	Enginner 11	OSS		08/11/2023	20/03/2023	08/02/2024	########
13	Enginner 12	SSM		08/11/2023	22/03/2023	12/03/2024	########				07/08/2023						09/08/2023
14	Enginner 13	Storage		08/11/2023	03/04/2023	15/03/2024	########				30/08/2023
15	Enginner 14	ODCM		15/11/2023	03/04/2023	27/02/2024	########				28/07/2023
16	Enginner 15	PSD	17/04/2024	08/11/2023	17/03/2023	08/02/2024	########		13/02/2023	01/02/2023		17/04/2024			17/04/2024			04/09/2023
17	Enginner 16	OSES	28/09/2023	25/11/2023	20/03/2023	22/02/2024	########			20/02/2023							21/07/2023
18	Enginner 17	OSS		09/11/2023	23/03/2023	07/02/2024	########
19	Enginner 18	OSES	27/09/2023	09/11/2023	17/03/2023	08/02/2024	########		04/08/2023	19/01/2023					10/02/2023	07/08/2023	07/08/2023
20	Enginner 19	GNO-A		22/11/2023	03/04/2023	07/02/2024	########
21	Enginner 20	OSES	12/09/2023	03/12/2023	17/03/2023	08/02/2024	########			24/02/2023					24/02/2023
22	Enginner 21	OSS		10/11/2023	17/03/2023	08/02/2024	########	27/02/2024			27/02/2024
23	Enginner 22	ODCM		08/11/2023	19/03/2023	14/02/2024	########		19/07/2023	05/02/2023
24	Enginner 23	OSS		09/11/2023	17/03/2023	09/02/2024	########
25	Enginner 24	ODCM	15/09/2023	09/11/2023	20/03/2023	22/02/2024	########				24/07/2023	17/08/2023				10/08/2023
26	Enginner 25	OSES	26/09/2023	08/11/2023	17/03/2023	26/02/2024	########			16/02/2023					16/02/2023
27	Enginner 26	GNO-A		13/11/2023	22/03/2023	09/02/2024	########
28	Enginner 27	OSES		14/11/2023	23/03/2023	27/02/2024	########	25/05/2023	25/05/2023		25/05/2023		09/05/2023				20/07/2023
29	Enginner 28	OSES	12/09/2023	27/11/2023	17/03/2023	21/02/2024	########				19/07/2023
30	Enginner 29	OSES	18/09/2023	27/11/2023	17/03/2023	25/03/2024	########				19/07/2023
31	Enginner 30	OSES	19/09/2023	08/11/2023	21/03/2023	09/02/2024	########			03/02/2023					03/02/2023
32	Enginner 31	GNO-A		08/11/2023	17/03/2023	08/02/2024	########
33
34
35	Engineer	Team	A Guide to Data Classification using CPI-810	Anti-Bribery and Anti-Corruption	CPNI Annual Training 2023	CPNI Annual Training 2024	Global Trade Compliance	Phishing - Don't Get Hooked!	Privacy and Information Security	Privileged Users 101	Reliability Matters: Episode 6 Maintenance Hygiene (VZ132307)	Reliability Matters: Episode 9 (2023)	Reliability Matters Episode 10: The Power of Prepare (2023)	Reliability Matters Episode 11: The Power of Pause (2023)	Reliability Matters Episode 12: The Power of Confirm (2023)	Reliability Matters: Episode 13 (2024)	Responsible Network Security and Protection - Term Server Ports and Passwords	Security Awareness: PCI, DSS, ISOO27001, NIST, The Privacy Act, and SSAE 16 & 18
36	Enginner 1	ODCM	Completed	Completed	Completed	Completed	Completed	#SPILL!	ACTION REQUIRED	ACTION REQUIRED	Completed	ACTION REQUIRED	ACTION REQUIRED	Completed	ACTION REQUIRED	ACTION REQUIRED	ACTION REQUIRED	ACTION REQUIRED
37	Enginner 2	GNO-A	ACTION REQUIRED	Completed	Completed	Completed	Completed	#SPILL!
38	Enginner 3	GNO-A	ACTION REQUIRED	Completed	Completed	ACTION REQUIRED	Completed	#SPILL!
39	Enginner 4	OSES	ACTION REQUIRED	Completed	Completed	Completed	Completed	#SPILL!
40	Enginner 5	ODCM	Completed	Completed	Completed	Completed	Completed	#SPILL!
41	Enginner 6	Storage	ACTION REQUIRED	Completed	Completed	Completed	Completed	#SPILL!
42	Enginner 7	ODCM	Completed	Completed	Completed	Completed	Completed	#SPILL!
43	Enginner 8	ODCM	Completed	Completed	Completed	Completed	Completed	#SPILL!
44	Enginner 9	PSD	ACTION REQUIRED	Completed	Completed	Completed	Completed	#SPILL!
45	Enginner 10	OSES	ACTION REQUIRED	Completed	Completed	Completed	Completed	#SPILL!
46	Enginner 11	OSS	ACTION REQUIRED	Completed	Completed	Completed	Completed	#SPILL!
47	Enginner 12	SSM	ACTION REQUIRED	Completed	Completed	Completed	Completed	#SPILL!
48	Enginner 13	Storage	ACTION REQUIRED	Completed	Completed	Completed	Completed	#SPILL!
49	Enginner 14	ODCM	ACTION REQUIRED	Completed	Completed	Completed	Completed	#SPILL!
50	Enginner 15	PSD	Completed	Completed	Completed	Completed	Completed	#SPILL!
51	Enginner 16	OSES	Completed	Completed	Completed	Completed	Completed	#SPILL!
52	Enginner 17	OSS	ACTION REQUIRED	Completed	Completed	Completed	Completed	#SPILL!
53	Enginner 18	OSES	Completed	Completed	Completed	Completed	Completed	#SPILL!
ISO Tracker (2)

 

[Fluff]

Thanks for that.
Maybe

=IF(min(LEN(H2:K2))=0, "ACTION REQUIRED", "Completed")

 

[Linsie]

Thanks for that.
Maybe

=IF(min(LEN(H2:K2))=0, "ACTION REQUIRED", "Completed")

thanks, that resolves the spill error, but it now returns action required for every entry, which is false.

I forgot to mention that I was doing the len formula to negate the issue where the formula will ignore the formulas in the orange table (they are not shown on the sheet above as I had to protect the names etc of the people so showed a copy with just the results on it.

 

[Fluff]

but it now returns action required for every entry, which is false.

In that case, you need to explain exactly what you are trying to do.

 

[Linsie]

Orange table shows the dates that the different course titles were completed.
The yellow table is to identify where action is needed by each engineer.

Some of the courses come under slightly different names but is the same course. I need to "merge" the findings on them courses and return just one completed or action required status in the yellow table.

So example is Yellow H36 needs the formula to look at orange H2:K2 and as K2 has a date in the cell return the overall status in yellow H36 as Completed.
Whereas yellow H37 should return a status of not completed as there are no dates in H3:K3

 

[Fluff]

In that case try

=IF(max(LEN(H2:K2))=0, "ACTION REQUIRED", "Completed")

 

[Linsie]

In that case try

=IF(max(LEN(H2:K2))=0, "ACTION REQUIRED", "Completed")

Amazing, thank you that one worked.

 

[Fluff]

You're welcome & thanks for the feedback.