Can you change the color or formatting of a cell based on the value of a cell on a different page?

[patfanrj]

Hello! My workbook is being used to track production for a specific task. There can be up to four different segments that can be invoiced for at one point, but sometimes, one or many of those may have already been invoiced. Right now, I have my main page which is keeping track of those individual segments, and then if some of them have been billed, I have to break said production into a new row and create a duplicate row of only what has NOT been billed.

Anyways, I have several pages being used for VLOOKUP formulas. Once I have a row completely filled out, I then have to go to another sheet and Ctrl+F a given segment name and see if it has been billed. I need a formula that will take my info from Columns D, G, J and M from Sheet 1, and then search for that segment in Column F on Sheet 2. If that segment has a match on Sheet 2, and if Column R says "YES", then it changes the format or text/cell color of the cell on Sheet 1 so that I know it has been invoiced previously. Is there a way to do this?

LAS 3GIS Connections Invoicing.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Date	Hub	Site Span NFID A	FQNID A	A Footage	Site Span NFID B	FQNID B	B Footage	Site Span NFID C	FQNID C	C Footage	Site Span NFID D	FQNID D	D Footage	Splice FQNID	Cut Sheet	Done?	Invoiced?
2	9/8/2021	Santa Ana MTSO	Not Found	FIB:BUR::500339569	260.60777948	1709ACAT.022	FIB:BUR::76350381	4959.20895045	N/A	N/A	N/A	N/A	N/A	N/A	FIB:SPLCL::500022094	SA MTSO_MH RED CABLE_CUT SHEET.xlsx	Yes	No
3	9/8/2021	Santa Ana MTSO	Not Found	FIB:BUR::500339199	260.60777948	1804BDBF.1	FIB:BUR::500523297	28.51819872	N/A	N/A	N/A	N/A	N/A	N/A	FIB:SPLCL::500022094	SA MTSO_ MH PINK CABLE_CUT SHEET.xlsx	Yes	No
4	9/8/2021	Santa Ana MTSO	1804BDBF.1	FIB:BUR::500523297	28.51819872	1709ACBJ.046	FIB:AER::500215396	3868.73856576	N/A	N/A	N/A	N/A	N/A	N/A	FIB:TRNSPLC::500374210	N/A (Other Side of Pink Cable)	Yes	No
5	9/8/2021	Santa Ana MTSO	1709ACAT.016	FIB:BUR::76350410	181.38566078	1709ACAT.017	FIB:BUR::76350320	510.28742111	1709ACAT.016	FIB:BUR::500067181	263.61187139	N/A	N/A	N/A	FIB:SPLCL::500054857	TALBERT AVE - BUSHARD ST _ CUT SHEET.xlsx	Yes	No
6	9/8/2021	Santa Ana MTSO	1709ACBJ.042	FIB:BUR::500524112	28.51819872	1709ACBJ.031	FIB:AER::500430955	10146.64005522	N/A	N/A	N/A	N/A	N/A	N/A	FIB:TRNSPLC::500374210	N/A (Olive Cable Outside Hub)	Yes	No
7	9/8/2021	Santa Ana MTSO	1709ACAT.028	FIB:AER::76350393	2800.16365830	1709ACBJ.037	FIB:AER::76000788	4070.61290782	N/A	N/A	N/A	1709ACAT.028	FIB:BUR::76350392	35.73569070	FIB:SPLCL::500148666	WARNER AVE - FLOWER ST_CUT SHEET_.xlsx	No	No
8	9/8/2021	Santa Ana MTSO	1709ACBJ.016	FIB:AER::76350537	983.63231605	1709ACBJ.016	FIB:BUR::76350536	1139.55990302	N/A	N/A	N/A	N/A	N/A	N/A	FIB:SPLCL::500274662	WASHINGTON AVE - BROADWAY _ CUT SHEET.xlsx	Yes	No
Data Inputs

Cell Formulas
Range	Formula
C2:C8	C2	=IF(ISNA(VLOOKUP(D2,'3GIS 09-16-21'!A:BW,61,FALSE)),"Not Found",VLOOKUP(D2,'3GIS 09-16-21'!A:BW,61,FALSE))
F2:F8	F2	=IF(ISNA(VLOOKUP(G2,'3GIS 09-16-21'!A:BW,61,FALSE)),"Not Found",VLOOKUP(G2,'3GIS 09-16-21'!A:BW,61,FALSE))
I2:I8	I2	=IF(ISNA(VLOOKUP(J2,'3GIS 09-16-21'!A:BW,61,FALSE)),"Not Found",VLOOKUP(J2,'3GIS 09-16-21'!A:BW,61,FALSE))
L2:L8	L2	=IF(ISNA(VLOOKUP(M2,'3GIS 09-16-21'!A:BW,61,FALSE)),"Not Found",VLOOKUP(M2,'3GIS 09-16-21'!A:BW,61,FALSE))

Named Ranges
Name	Refers To	Cells
'3GIS 09-16-21'!_FilterDatabase	='3GIS 09-16-21'!$A$1:$BW$5291	L2:L8, I2:I8, F2:F8, C2:C8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:C62,F1:F62,I1:I62,L1:L62,L80,I80,F80,C80,C82:C1048576,F82:F1048576,I82:I1048576,L82:L1048576,L64:L78,I64:I78,F64:F78,C64:C78	Cell Value	contains "Not Found"	text	NO
O160:O162,O1:O62,O164,O166,O168,O170,O172,O74:O75,O77:O78,O88:O158,O175:O1048576,O80,O82:O86,O64:O72	Cell Value	contains "TRNSPLC"	text	NO

LAS 3GIS Connections Invoicing.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M				Q	R
1	WORK ORDER	HUB	City	County	FQN ID	Site Span NFID	Cluster Ring NFID	Calculated Length	Fiber Count	Inventory Status Code	Network Type	FIBER CONNECTIONS				Production	Billed
2	LSA_S_1709ACAV_017_SCL_ORANGE_MALL_G1	ASTORIA	Orange	Orange	FIB:AER::76000274	1709ACAV.017	1709ACAV	1,763.16	432	Proposed	FrontHaul	TRUE				01/18/2021	YES
3	LSA_S_1709ACAV_017_SCL_ORANGE_MALL_G1	ASTORIA	Orange	Orange	FIB:AER::76000276	1709ACAV.017	1709ACAV	1,793.88	432	Proposed	FrontHaul	TRUE				01/18/2021	YES
4	LSA_S_1709ACAV_017_SCL_ORANGE_MALL_G1	ASTORIA	Orange	Orange	FIB:BUR::500438907	1709ACAV.017	1709ACAV	10.99	432	Proposed	FrontHaul	TRUE				01/18/2021	YES
5	LSA_S_1709ACAV_017_SCL_ORANGE_MALL_G1	ASTORIA	Orange	Orange	FIB:TAIL::500271391	1709ACAV.017	1709ACAV	3.13	24	Proposed	FrontHaul	TRUE				01/18/2021	YES
6	LSA_S_1709ACAV_008_SCL_AUBURN_G2	ASTORIA	Anaheim	Orange	FIB:BUR::76000286	1709ACAV.008	1709ACAV	2,615.15	864	Proposed	FrontHaul	TRUE				01/20/2021	YES
7	LSA_S_1709ACAV_008_SCL_AUBURN_G2	ASTORIA	Anaheim	Orange	FIB:TAIL::500299178	1709ACAV.008	1709ACAV	303.64	24	Proposed	FrontHaul	TRUE				01/20/2021	YES
8	LSA_S_1709ACAI_025_IMPERIAL_CYN	ASTORIA	Anaheim	Orange	FIB:BUR::500015239	1709ACAI.025	1709ACAI	2,035.00	432	Proposed	FrontHaul	TRUE				01/20/2021	YES
9	LSA_S_1709ACAI_025_IMPERIAL_CYN	ASTORIA	Anaheim	Orange	FIB:BUR::76000293	1709ACAI.025	1709ACAI	254.08	432	Proposed	FrontHaul	TRUE				01/20/2021	YES
10	LSA_S_1709ACAI_31_SCL_ANAHEIM_100	ASTORIA	Anaheim	Orange	FIB:BUR::500015333	1709ACAI.31	1709ACAI	2,309.16	432	Proposed	FrontHaul	TRUE				01/21/2021	YES
11	LSA_S_1709ACAV_002_SCL_ANAHEIM_85	ASTORIA	Anaheim	Orange	FIB:BUR::76000289	1709ACAV.002	1709ACAV	876.41	432	Proposed	FrontHaul	TRUE				01/21/2021	YES
12	LSA_S_1709ACAV_002_SCL_ANAHEIM_85	ASTORIA	Anaheim	Orange	FIB:BUR::76000303	1709ACAV.002	1709ACAV	726.09	432	Proposed	FrontHaul	TRUE				01/21/2021	YES
13	LSA_S_1709ACAV_003_SCL_ANAHEIM_86	ASTORIA	Anaheim	Orange	FIB:BUR::76000298	1709ACAV.003	1709ACAV	1,951.00	432	Proposed	FrontHaul	TRUE				01/21/2021	YES
14	LSA_S_1709ACAV_003_SCL_ANAHEIM_86	ASTORIA	Anaheim	Orange	FIB:BUR::76637584	1709ACAV.003	1709ACAV	536.51	72	Proposed	FrontHaul	TRUE				01/21/2021	YES
Fiber Connections

Cell Formulas
Range	Formula
D2:D14	D2	=INDEX('C:\Users\rjgab\Downloads\[LA SOUTH COMPLETE TRACKER.xlsx]VLOOKUP SHEET'!E:E,MATCH(G2,'C:\Users\rjgab\Downloads\[LA SOUTH COMPLETE TRACKER.xlsx]VLOOKUP SHEET'!F:F,0))
E2:E14	E2	=INDEX('C:\Users\rjgab\Downloads\[LA SOUTH COMPLETE TRACKER.xlsx]VLOOKUP SHEET'!D:D,MATCH(G2,'C:\Users\rjgab\Downloads\[LA SOUTH COMPLETE TRACKER.xlsx]VLOOKUP SHEET'!F:F,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[etaf]

In answer to your title - yes you can
But i dont follow the examples you gave
i dont know what sheet1 is
anyway

Columns D, G, J and M from Sheet 1

then
Search in column F

sorry no idea

BUT you can use a COUNTIFS() to see if the value exists to use in conditional formatting
not sure what a segment is

COUNTIFS( range in lookup sheet , value in D , range in lookup sheet , "YES" )
then see if its >0
and colour

conditional formatting rule, would be
=COUNTIFS( range in lookup sheet , value in D , range in lookup sheet , "YES" ) > 0

perhaps a simple sheet would help , with expected results shown