Filter question

[angleright]

I am trying to filter on a column(column "L",Plan No) with the following formula
=IF(R18="","",FILTER(FILTER($H$2:$N$109,($L$2:$L$109=$R$18),"NOTHING FOUND"),{0,0,0,0,0,0,1}))
the filter returns work great if the cell contains a number and letter but returns nothing if the cell only contains a number. I have the cells formatted as text. I've tried formatting as general and numeric but still get the same results.
the formula is in row w59

 

[Fluff]

but returns nothing if the cell only contains a number

Which cell? R18 or col L?

 

[angleright]

Which cell? R18 or col L?

Which cell? R18 or col L?

L would be the column that is being filtered. As i said works great with alpha-numeric but not with just numeric value in the cell, even though cells are formatted as text

 

[Fluff]

But which cell(s) have just a number?

 

[angleright]

But which cell(s) have just a number?

Column L, some cells are alpha numeric and the others are just numeric, cell r18 is where the value of the search criteria is entered. in other words to find all plan numbers "323-R" it will return several instances, if I put just "1234" nothing is returned, although i do know it exists.

Thanks for looking at this

 

[Fluff]

Ok, how about

=IF(R18="","",FILTER(N2:N109,L2:L109=R18&"","NOTHING FOUND"))

 

[angleright]

no, still only returns value if it's alpha numeric. Won't work on numeric values

BETA POST.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	BOX	BLDG DEPT	ADDRESS	STREET	PLAN NO	PART NO	N	O	\\CF-FILE04\Engineering\ScanWorks\
2	028	BLDG DEPT	1900	23RD ST	1874-R	L	\\CF-FILE04\Engineering\ScanWorks\028 BLDG DEPT 1900 23RD ST L.PDF	Open		NO. OF SCANNED BOXES
3	028	BLDG DEPT	190	VAN DOREN	302-R		\\CF-FILE04\Engineering\ScanWorks\028 BLDG DEPT 190 VAN DOREN.PDF	Open			76
4	028	BLDG DEPT	1817	FRONT ST	351-R		\\CF-FILE04\Engineering\ScanWorks\028 BLDG DEPT 1817 FRONT ST.PDF	Open
5	028	BLDG DEPT	1705	CALVERT DR	395-R		\\CF-FILE04\Engineering\ScanWorks\028 BLDG DEPT 1705 CALVERT DR.PDF	Open
6	028	BLDG DEPT	140	PORTAGE TRL	322-R		\\CF-FILE04\Engineering\ScanWorks\028 BLDG DEPT 140 PORTAGE TRL.PDF	Open
7	028	BLDG DEPT	1380-82	9TH ST	332-R		\\CF-FILE04\Engineering\ScanWorks\028 BLDG DEPT 1380-82 9TH ST.PDF	Open
8	028	BLDG DEPT	1372-74	9TH ST	330-R		\\CF-FILE04\Engineering\ScanWorks\028 BLDG DEPT 1372-74 9TH ST.PDF	Open			SEARCH BY ADDRESS (MUST BE EXACT MATCH)
9	028	BLDG DEPT	1348	FRONT ST	317-R		\\CF-FILE04\Engineering\ScanWorks\028 BLDG DEPT 1348 FRONT ST.PDF	Open			ENTER ST NO.	ENTER ST. NAME
10	028	BLDG DEPT	1234	PORTAGE TRL	391-R	A	\\CF-FILE04\Engineering\ScanWorks\028 BLDG DEPT 1234 PORTAGE TRL A.PDF	Open			827	shannon		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 827 SHANNON.PDF
11	028	BLDG DEPT	1234	PORTAGE TRL	391-R	B	\\CF-FILE04\Engineering\ScanWorks\028 BLDG DEPT 1234 PORTAGE TRL B.PDF	Open
12	028	BLDG DEPT	1027	MUNROE FALLS AVE		A	\\CF-FILE04\Engineering\ScanWorks\028 BLDG DEPT 1027 MUNROE FALLS AVE A.PDF	Open
13	028	BLDG DEPT	1027	MUNROE FALLS AVE	331-R	B	\\CF-FILE04\Engineering\ScanWorks\028 BLDG DEPT 1027 MUNROE FALLS AVE B.PDF	Open
14	028	BLDG DEPT	1025	MUNROE FALLS AVE		A	\\CF-FILE04\Engineering\ScanWorks\028 BLDG DEPT 1025 MUNROE FALLS AVE A.PDF	Open
15	028	BLDG DEPT	1025	MUNROE FALLS AVE	331-R	B	\\CF-FILE04\Engineering\ScanWorks\028 BLDG DEPT 1025 MUNROE FALLS AVE B.PDF	Open
16	028	BLDG DEPT	1023	MUNROE FALLS AVE		A	\\CF-FILE04\Engineering\ScanWorks\028 BLDG DEPT 1023 MUNROE FALLS AVE A.PDF	Open			SEARCH BY PLAN NO.
17	028	BLDG DEPT	1023	MUNROE FALLS AVE	331-R	B	\\CF-FILE04\Engineering\ScanWorks\028 BLDG DEPT 1023 MUNROE FALLS AVE B.PDF	Open			ENTER PLAN NO.
18	028	BLDG DEPT		STATE RD AND CHAMBERLIN AVE	353-R		\\CF-FILE04\Engineering\ScanWorks\028 BLDG DEPT STATE RD AND CHAMBERLIN AVE.PDF	Open							#N/A
19	029	BLDG DEPT	915-917	SACKETT	118		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 915-917 SACKETT.PDF	Open
20	029	BLDG DEPT	835	SHANNON	7		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 835 SHANNON.PDF	Open			SEARCH BY NAME ONLY(I.E., "FRATERNAL ORDER OF EAGLES")
21	029	BLDG DEPT	827	SHANNON	7		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 827 SHANNON.PDF	Open			FOR WILD CARD USE "*"
22	029	BLDG DEPT	810	PORTAGE	15		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 810 PORTAGE.PDF	Open			ENTER NAME
23	029	BLDG DEPT	81	WASHINGTON	3		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 81 WASHINGTON.PDF	Open
24	029	BLDG DEPT	792	GRAHAM RD	82		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 792 GRAHAM RD.PDF	Open
25	029	BLDG DEPT	790	GRAHAM RD	82		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 790 GRAHAM RD.PDF	Open
26	029	BLDG DEPT	788	GRAHAM RD	82		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 788 GRAHAM RD.PDF	Open
27	029	BLDG DEPT	767	CHARLES ST	40		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 767 CHARLES ST.PDF	Open			SEARCH RESULTS BY ADDRESS
28	029	BLDG DEPT	759	ORRVILLE	41		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 759 ORRVILLE.PDF	Open
29	029	BLDG DEPT	755	ALAMEDA	26		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 755 ALAMEDA.PDF	Open			BOX NO.	SEARCH RESULTS FOR			RETURN CONVERTED TO TEXT	FILTER RETURN
30	029	BLDG DEPT	755	ROANOKE	26		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 755 ROANOKE.PDF	Open			029	OPEN			\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 827 SHANNON.PDF	\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 827 SHANNON.PDF
31	029	BLDG DEPT	753	ORRVILLE	41		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 753 ORRVILLE.PDF	Open
32	029	BLDG DEPT	747-49	HOLLYWOOD	119		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 747-49 HOLLYWOOD.PDF	Open
33	029	BLDG DEPT	747	ORRVILLE	41		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 747 ORRVILLE.PDF	Open
34	029	BLDG DEPT	742	FRANCIES AVE	41		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 742 FRANCIES AVE.PDF	Open
35	029	BLDG DEPT	741-43	HOLLYWOOD	119		\\CF-FILE04\Engineering\ScanWorks\029 BLDG DEPT 741-43 HOLLYWOOD.PDF	Open
36
CCFTABLE

Cell Formulas
Range	Formula
N2,N10:N17	N2	=HYPERLINK(CONCATENATE($P$1&H2&I2& J2&" "&K2&" "&M2&".PDF"))
O2:O35	O2	=HYPERLINK(N2,"Open")
N3:N9,N19:N35	N3	=HYPERLINK(CONCATENATE($P$1&H3&I3& J3&" "&K3&M3&".PDF"))
N18	N18	=HYPERLINK(CONCATENATE($P$1&H18&I18&K18&".PDF"))
R3	R3	=SHEETS()-2
U10	U10	=INDEX(N:N,MATCH(1,(R10=J:J)*(S10=K:K),0),1)
V18	V18	=VLOOKUP($R$18,$L$2:$N$109,3,FALSE)
R19	R19	=IF(R18="","",HYPERLINK(V18,"OPEN"))
U23	U23	=IF(R23="","",VLOOKUP(R23&"*",K2:N109,4,FALSE))
R24	R24	=IF(R23="","",HYPERLINK(U23,"OPEN"))
R30	R30	=IF(S10="","",FILTER(FILTER($H$2:$N$109,($K$2:$K$109=$S$10)*($J$2:$J$109=$R$10),"NOTHING FOUND"),{1,0,0,0,0,0,0}))
W30	W30	=FILTER(FILTER($H$2:$N$109,($K$2:$K$109=$S$10)*($J$2:$J$109=$R$10),"NOTHING FOUND"),{0,0,0,0,0,0,1})
S30	S30	=IF(S10="","",(HYPERLINK(V30,"OPEN")))
S31:S36	S31	=IF(V31="","",(HYPERLINK(V31,"OPEN")))
V30:V36	V30	=IF(W30="","",ARRAYTOTEXT(W30))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:O109	Expression	=MOD(ROW(),2)=1	text	NO
H2:O109	Expression	=MOD(ROW(),2)=1	text	NO
H2:P109	Expression	=MOD(ROW(),2)=1	text	NO

 

[Fluff]

The formula you said didn't work is not even in that sheet, but the two formulae using Filter seem to be working.

 

[angleright]

Sorry Fluff, formula is in w60

BETA FILE.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	BOX	BLDG DEPT	ADDRESS	STREET	PLAN NO	PART NO	N	O	\\CF-FILE04\Engineering\ScanWorks\
2	001	BLDG DEPT	2960	BAILEY RD		A	\\CF-FILE04\Engineering\ScanWorks\001 BLDG DEPT 2960 BAILEY RD A.PDF	Open		NO. OF SCANNED BOXES
3	001	BLDG DEPT	2960	BAILEY RD		B	\\CF-FILE04\Engineering\ScanWorks\001 BLDG DEPT 2960 BAILEY RD B.PDF	Open			76
4	001	BLDG DEPT	2916	OAKWOOD DR	A-9-R		\\CF-FILE04\Engineering\ScanWorks\001 BLDG DEPT 2916 OAKWOOD DR.PDF	Open
5	001	BLDG DEPT	2819	HUDSON DR	A-2-R		\\CF-FILE04\Engineering\ScanWorks\001 BLDG DEPT 2819 HUDSON DR.PDF	Open
6	001	BLDG DEPT	2231	BROAD BLVD	A-10-R		\\CF-FILE04\Engineering\ScanWorks\001 BLDG DEPT 2231 BROAD BLVD.PDF	Open
7	001	BLDG DEPT	2121	6TH ST	A-11-R		\\CF-FILE04\Engineering\ScanWorks\001 BLDG DEPT 2121 6TH ST.PDF	Open
8	001	BLDG DEPT	2114	FRONT ST	A-8-R	A	\\CF-FILE04\Engineering\ScanWorks\001 BLDG DEPT 2114 FRONT ST A.PDF	Open			SEARCH BY ADDRESS (MUST BE EXACT MATCH)
9	001	BLDG DEPT	2114	FRONT ST	A-8-R	B	\\CF-FILE04\Engineering\ScanWorks\001 BLDG DEPT 2114 FRONT ST B.PDF	Open			ENTER ST NO.	ENTER ST. NAME
10	001	BLDG DEPT	210	BROADWAY EAST	A-7-R		\\CF-FILE04\Engineering\ScanWorks\001 BLDG DEPT 210 BROADWAY EAST.PDF	Open						0
11	001	BLDG DEPT	210	E BROADWAY	A-6-R		\\CF-FILE04\Engineering\ScanWorks\001 BLDG DEPT 210 E BROADWAY.PDF	Open
12	001	BLDG DEPT	1640	STATE RD	A-1-R		\\CF-FILE04\Engineering\ScanWorks\001 BLDG DEPT 1640 STATE RD.PDF	Open
13	001	BLDG DEPT	1305	MONROE FALLS AVE	A-12-R		\\CF-FILE04\Engineering\ScanWorks\001 BLDG DEPT 1305 MONROE FALLS AVE.PDF	Open
14	001	BLDG DEPT	117	PORTAGE TRL	A-5-R		\\CF-FILE04\Engineering\ScanWorks\001 BLDG DEPT 117 PORTAGE TRL.PDF	Open
15	001	BLDG DEPT		CITY SERVICE OIL CO	A-3-R	A	\\CF-FILE04\Engineering\ScanWorks\001 BLDG DEPT CITY SERVICE OIL CO A.PDF	Open
16	001	BLDG DEPT		CITY SERVICE OIL CO	A-3-R	B	\\CF-FILE04\Engineering\ScanWorks\001 BLDG DEPT CITY SERVICE OIL CO B.PDF	Open			SEARCH BY PLAN NO.
17	002	BLDG DEPT	507	MONROE FALLS AVE	A-17-R		\\CF-FILE04\Engineering\ScanWorks\002 BLDG DEPT 507 MONROE FALLS AVE.PDF	Open			ENTER PLAN NO.
18	002	BLDG DEPT	2686	FRONT ST	A-13-R	A	\\CF-FILE04\Engineering\ScanWorks\002 BLDG DEPT 2686 FRONT ST A.PDF	Open							#N/A
19	002	BLDG DEPT	2686	FRONT ST	A-13-R	B	\\CF-FILE04\Engineering\ScanWorks\002 BLDG DEPT 2686 FRONT ST B.PDF	Open
20	002	BLDG DEPT	2307	SACKETT ST	A-19-R		\\CF-FILE04\Engineering\ScanWorks\002 BLDG DEPT 2307 SACKETT ST.PDF	Open			SEARCH BY NAME ONLY(I.E., "FRATERNAL ORDER OF EAGLES")
21	002	BLDG DEPT	2231	BROAD BLVD	A-21-R		\\CF-FILE04\Engineering\ScanWorks\002 BLDG DEPT 2231 BROAD BLVD.PDF	Open			FOR WILD CARD USE "*"
22	002	BLDG DEPT	2063	FRONT ST	A-18-R		\\CF-FILE04\Engineering\ScanWorks\002 BLDG DEPT 2063 FRONT ST.PDF	Open			ENTER NAME
23	002	BLDG DEPT	1916	HIGHBRIDGE RD	A-16-R		\\CF-FILE04\Engineering\ScanWorks\002 BLDG DEPT 1916 HIGHBRIDGE RD.PDF	Open
24	002	BLDG DEPT	1787	STATE RD	A-20-R		\\CF-FILE04\Engineering\ScanWorks\002 BLDG DEPT 1787 STATE RD.PDF	Open
25	002	BLDG DEPT	1752	WILLIAMS ST	A-15-R		\\CF-FILE04\Engineering\ScanWorks\002 BLDG DEPT 1752 WILLIAMS ST.PDF	Open
26	002	BLDG DEPT	1225	MONROE FALLS AVE	C-6705		\\CF-FILE04\Engineering\ScanWorks\002 BLDG DEPT 1225 MONROE FALLS AVE.PDF	Open
27	002	BLDG DEPT		STATE RD AND CHESTNUT BLVD	A-14-R		\\CF-FILE04\Engineering\ScanWorks\002 BLDG DEPT STATE RD AND CHESTNUT BLVD.PDF	Open			SEARCH RESULTS BY ADDRESS
28	002	BLDG DEPT		FRATERNAL ORDER OF EAGLES	A-22-R		\\CF-FILE04\Engineering\ScanWorks\002 BLDG DEPT FRATERNAL ORDER OF EAGLES.PDF	Open
29	003	BLDG DEPT	800	TALLMADGE RD	A-29-R	A	\\CF-FILE04\Engineering\ScanWorks\003 BLDG DEPT 800 TALLMADGE RD A.PDF	Open			BOX NO.	SEARCH RESULTS FOR			RETURN CONVERTED TO TEXT	FILTER RETURN
30	003	BLDG DEPT	800	TALLMADGE RD	A-29-R	B	\\CF-FILE04\Engineering\ScanWorks\003 BLDG DEPT 800 TALLMADGE RD B.PDF	Open							#VALUE!	#VALUE!
31	003	BLDG DEPT	3131	W BAILEY RD	A-28-R		\\CF-FILE04\Engineering\ScanWorks\003 BLDG DEPT 3131 W BAILEY RD.PDF	Open
32	003	BLDG DEPT	2300	4TH ST	A-27-R		\\CF-FILE04\Engineering\ScanWorks\003 BLDG DEPT 2300 4TH ST.PDF	Open
33	003	BLDG DEPT	1910	SEARL ST	A-26-R		\\CF-FILE04\Engineering\ScanWorks\003 BLDG DEPT 1910 SEARL ST.PDF	Open
34	003	BLDG DEPT		YMCA		A	\\CF-FILE04\Engineering\ScanWorks\003 BLDG DEPT YMCA A.PDF	Open
35	003	BLDG DEPT		NEWBERRY SCHOOL		A	\\CF-FILE04\Engineering\ScanWorks\003 BLDG DEPT NEWBERRY SCHOOL A.PDF	Open
36	003	BLDG DEPT		ELIZABETH PRICE SCHOOL		A	\\CF-FILE04\Engineering\ScanWorks\003 BLDG DEPT ELIZABETH PRICE SCHOOL A.PDF	Open
37	003	BLDG DEPT		CRAWFORD SCHOOL		A	\\CF-FILE04\Engineering\ScanWorks\003 BLDG DEPT CRAWFORD SCHOOL A.PDF	Open
38	003	BLDG DEPT		YMCA		B	\\CF-FILE04\Engineering\ScanWorks\003 BLDG DEPT YMCA B.PDF	Open
39	003	BLDG DEPT		NEWBERRY SCHOOL		B	\\CF-FILE04\Engineering\ScanWorks\003 BLDG DEPT NEWBERRY SCHOOL B.PDF	Open
40	003	BLDG DEPT		ELIZABETH PRICE SCHOOL		B	\\CF-FILE04\Engineering\ScanWorks\003 BLDG DEPT ELIZABETH PRICE SCHOOL B.PDF	Open
41	003	BLDG DEPT		CRAWFORD SCHOOL		B	\\CF-FILE04\Engineering\ScanWorks\003 BLDG DEPT CRAWFORD SCHOOL B.PDF	Open
42	003	BLDG DEPT		NEWBERRY SCHOOL		C	\\CF-FILE04\Engineering\ScanWorks\003 BLDG DEPT NEWBERRY SCHOOL C.PDF	Open
43	003	BLDG DEPT		ELIZABETH PRICE SCHOOL		C	\\CF-FILE04\Engineering\ScanWorks\003 BLDG DEPT ELIZABETH PRICE SCHOOL C.PDF	Open
44	003	BLDG DEPT		SILL SCHOOL PLUMBING			\\CF-FILE04\Engineering\ScanWorks\003 BLDG DEPT SILL SCHOOL PLUMBING.PDF	Open
45	004	BLDG DEPT	355	PORTAGE TRL	A-33-R		\\CF-FILE04\Engineering\ScanWorks\004 BLDG DEPT 355 PORTAGE TRL.PDF	Open
46	004	BLDG DEPT	2916	OAKWOOD DR	A-39-R		\\CF-FILE04\Engineering\ScanWorks\004 BLDG DEPT 2916 OAKWOOD DR.PDF	Open
47	004	BLDG DEPT	2834	2ND ST	A-35-R		\\CF-FILE04\Engineering\ScanWorks\004 BLDG DEPT 2834 2ND ST.PDF	Open
48	004	BLDG DEPT	2735	FRONT ST	A-38-R	A	\\CF-FILE04\Engineering\ScanWorks\004 BLDG DEPT 2735 FRONT ST A.PDF	Open
49	004	BLDG DEPT	2735	FRONT ST	A-38-R	B	\\CF-FILE04\Engineering\ScanWorks\004 BLDG DEPT 2735 FRONT ST B.PDF	Open
50	004	BLDG DEPT	2421	STATE RD	A-37-R		\\CF-FILE04\Engineering\ScanWorks\004 BLDG DEPT 2421 STATE RD.PDF	Open
51	004	BLDG DEPT	1850	STATE RD	A-36-R	A	\\CF-FILE04\Engineering\ScanWorks\004 BLDG DEPT 1850 STATE RD A.PDF	Open
52	004	BLDG DEPT	1850	STATE RD	A-36-R	B	\\CF-FILE04\Engineering\ScanWorks\004 BLDG DEPT 1850 STATE RD B.PDF	Open
53	004	BLDG DEPT	1850	STATE RD	A-36-R	C	\\CF-FILE04\Engineering\ScanWorks\004 BLDG DEPT 1850 STATE RD C.PDF	Open
54	004	BLDG DEPT	1850	STATE RD	A-36-R	D	\\CF-FILE04\Engineering\ScanWorks\004 BLDG DEPT 1850 STATE RD D.PDF	Open
55	004	BLDG DEPT		STATE RD AND CHAMBERLIN	A-41-R	A	\\CF-FILE04\Engineering\ScanWorks\004 BLDG DEPT STATE RD AND CHAMBERLIN A.PDF	Open			SEARCH RESULTS BY PLAN NO.
56	004	BLDG DEPT		STATE RD AND CHAMBERLIN	A-41-R	B	\\CF-FILE04\Engineering\ScanWorks\004 BLDG DEPT STATE RD AND CHAMBERLIN B.PDF	Open
57	004	BLDG DEPT		STATE RD AND FALLS ST			\\CF-FILE04\Engineering\ScanWorks\004 BLDG DEPT STATE RD AND FALLS ST.PDF	Open			BOX NO.	SEARCH RESULTS FOR
58	004	BLDG DEPT		FRATERNAL ORDER OF EAGLES	A-34-R		\\CF-FILE04\Engineering\ScanWorks\004 BLDG DEPT FRATERNAL ORDER OF EAGLES.PDF	Open
59	005	BLDG DEPT	807	WASHINGTON	A-75		\\CF-FILE04\Engineering\ScanWorks\005 BLDG DEPT 807 WASHINGTON.PDF	Open							RETURN CONVERTED TO TEXT	FILTER RETURN
60	005	BLDG DEPT	739	PORTAGE TRL			\\CF-FILE04\Engineering\ScanWorks\005 BLDG DEPT 739 PORTAGE TRL.PDF	Open
CCFTABLE

Cell Formulas
Range	Formula
N2:N3,N8:N9,N18:N19,N29:N30,N48:N49,N51:N54	N2	=HYPERLINK(CONCATENATE($P$1&H2&I2& J2&" "&K2&" "&M2&".PDF"))
O2:O60	O2	=HYPERLINK(N2,"Open")
N4:N7,N10:N13,N17,N20:N26,N31:N33,N45:N47,N50,N59:N60	N4	=HYPERLINK(CONCATENATE($P$1&H4&I4& J4&" "&K4&M4&".PDF"))
N14	N14	=HYPERLINK(CONCATENATE($P$1&H14&I14&J14&" "&K14&M14&".PDF"))
N15:N16,N34:N43,N55:N56	N15	=HYPERLINK(CONCATENATE($P$1&H15&I15& J15&K15&" "&M15&".PDF"))
N27:N28,N44,N57:N58	N27	=HYPERLINK(CONCATENATE($P$1&H27&I27&K27&".PDF"))
R3	R3	=SHEETS()-2
U10	U10	=INDEX(N:N,MATCH(1,(R10=J:J)*(S10=K:K),0),1)
V18	V18	=VLOOKUP($R$18,$L$2:$N$6895,3,FALSE)
R19	R19	=IF(R18="","",HYPERLINK(V18,"OPEN"))
U23	U23	=IF(R23="","",VLOOKUP(R23&"*",K2:N6895,4,FALSE))
R24	R24	=IF(R23="","",HYPERLINK(U23,"OPEN"))
R30	R30	=IF(S10="","",FILTER(FILTER($H$2:$N$6895,($K$2:$K$6895=$S$10)*($J$2:$J$6895=$R$10),"NOTHING FOUND"),{1,0,0,0,0,0,0}))
W30	W30	=FILTER(FILTER($H$2:$N$6895,($K$2:$K$6895=$S$10)*($J$2:$J$6895=$R$10),"NOTHING FOUND"),{0,0,0,0,0,0,1})
S30	S30	=IF(S10="","",(HYPERLINK(V30,"OPEN")))
S31:S54,S60	S31	=IF(V31="","",(HYPERLINK(V31,"OPEN")))
S58	S58	=IF(R18="","",R18)
V30:V58,V60	V30	=IF(W30="","",ARRAYTOTEXT(W30))
R60	R60	=IF(R18="","",FILTER(FILTER($H$2:$N$6895,($L$2:$L$6895=$R$18),"NOTHING FOUND"),{1,0,0,0,0,0,0}))
W60	W60	=IF(R18="","",FILTER(FILTER($H$2:$N$6895,($L$2:$L$6895=$R$18),"NOTHING FOUND"),{0,0,0,0,0,0,1}))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:O2	Expression	=MOD(ROW(),2)=1	text	NO
H2:O2	Expression	=MOD(ROW(),2)=1	text	NO
H2:P2	Expression	=MOD(ROW(),2)=1	text	NO
H3:O3	Expression	=MOD(ROW(),2)=1	text	NO
H3:O3	Expression	=MOD(ROW(),2)=1	text	NO
H3:P3	Expression	=MOD(ROW(),2)=1	text	NO
H4:O1900,H1901:K2361,M1901:O2361,H2362:O5465,H5286:H5551	Expression	=MOD(ROW(),2)=1	text	NO
H4:O1900,H1901:K2361,M1901:O2361,H2362:O5465,H5286:H5551	Expression	=MOD(ROW(),2)=1	text	NO
H4:P1900,H5908:O5908,H5924:O5924,H5958:O5958,H5870:O5870,H5872:O5872,H5874:O5874,H5876:O5876,H5878:O5878,H5880:O5880,H5882:O5882,H5884:O5884,H5886:O5886,H5888:O5888,H5890:O5890,H5892:O5892,H5894:O5894,H5896:O5896,H5898:O5898,H5900:O5900,H5902:O5902	Expression	=MOD(ROW(),2)=1	text	NO

 

[Fluff]

There's nothing in R18 & there are no numeric values in col L