Filter/Xlookup error

J

Jedi Master

Board Regular
Joined
Jun 10, 2024
Messages
70
Office Version
  1. 365
Platform
  1. Windows
Hello Everyone! Would someone be able to help me with understanding and fixing the error I am getting when trying to filter results? It is finding the correct results(Name values based on date entered and hours >0), but returning a portion of them as errors. When I go through the calculation steps it shows that it finds the correct values but returns some portion of the list as #N/A:

1719322257757.png


1719321948603.png

Below is the referenced PM Project tracker in the above formula bar:

1719322051445.png
 

Attachments

  • 1719321880020.png
    1719321880020.png
    24.4 KB · Views: 2
Sort by date Sort by votes
Jedi Master said:
New Bid Master.xlsm
BC
9LaborerJohnny
10ForemanTimmy
11LaborerTommy
12LaborerSally
13ForemanTrent
14LaborerLisa
15 Bert
16OperatorSam
17  
18  
19  
20  
21  
PM Project Tracker
Cell Formulas
RangeFormula
B9:B21B9=IF(ForemanProjectTracker[@Column1]="","",ForemanProjectTracker[@Column1])
C9:C21C9=IF(ForemanProjectTracker[@Column2]="","",ForemanProjectTracker[@Column2])

New Bid Master.xlsm
M
98
108
110
120
1310
140
150
1610
170
180
190
200
210
PM Project Tracker
Cell Formulas
RangeFormula
M9:M21M9=ForemanProjectTracker[@Column12]
Cells with Conditional Formatting
CellConditionCell FormatStop If True
W9:AC21,AG9:AM21,AQ9:AW21,AG24:AM37,W24:AC37,AG41:AM108,AQ24:AW37,BA24:BG37,BA9:BG21,W39:AC108,AQ39:AW108,BA39:BG108,OC38:OI40,OC58:OH108,OC56:OI57,OC41:OH55,OC24:OH37,M41:S108,M24:S37,M9:S21Cell Value>0textNO
M11:S11Expression=OR(M6=M11,M6>I11, M6<J11,M6=I11)textNO
Click to expand...
Foreman Project Tracker below: feeds PM Project Tracker
New Bid Master.xlsm
BCDEFMNOPQR
5Jun 24, 2024
6Labor Tracking242526272829
7PositionEmployee NameBudget HrsHrs Used% usedMTWTFS
8
9LaborerJohnny1100105395.73%889281000
10ForemanTimmy2754215.27%810888
11LaborerTommy110080.73%8
12LaborerSally110080.73%8
13ForemanTrent2755018.18%1010101010
14LaborerLisa110000.00%
15Bert 0 
16OperatorSam010 10
17 0 
18 0 
19 0 
20 0 
21 0 
Foreman Project Tracker
Cell Formulas
RangeFormula
M5M5=M6
M6M6=Project_Start
N6:R6N6=M6+1
M7:R7M7=LEFT(TEXT(M6,"ddd"),1)
D9D9=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
E9:E21E9=SUM(M9:S9,W9:AC9,AG9:AM9,AQ9:AW9,BA9:BG9,BK9:BQ9,BU9:CA9,CE9:CK9,CO9:CU9,CY9:DE9,DI9:DO9,DS9:DY9,EC9:EI9,EM9:ES9,EW9:FC9,FG9:FM9,FQ9:FW9,GA9:GG9,GK9:GQ9,GU9:HA9,HE9:HK9,HO9:HU9,HY9:IE9,II9:IO9,IS9:IY9,JC9:JI9,JM9:JS9,JW9:KC9,KG9:KM9,KQ9:KW9,LA9:LG9,LK9:LQ9,LU9:MA9,ME9:MK9,MO9:MU9,MY9:NE9,NI9:NO9,NS9:NY9,OC9:OI9,OM9:OS9,OW9:PC9,PG9:PM9,PQ9:PW9,QA9:QG9,QK9:QQ9,QU9:RA9,RE9:RK9,RO9:RU9,RY9:SE9,SI9:SO9,SS9:SY9,TC9:TI9,TM9:TS9,TW9:UC9,UG9:UM9)
F9:F16F9=IF(D9="","",IFERROR(E9/D9,""))
D10D10=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D11D11=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D12D12=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D13D13=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D14D14=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D15D15=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D16D16=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D17D17=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
F17:F21F17=IF(D17="","",E17/D17)
D18D18=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D19D19=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D20D20=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D21D21=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
Named Ranges
NameRefers ToCells
'Foreman Project Tracker'!Project_Start='Foreman Project Tracker'!$I$4M6
Cells with Conditional Formatting
CellConditionCell FormatStop If True
W9:AC21,AG9:AM21,AQ9:AW21,BA9:BG21,W24:AC38,AG24:AM38,AQ24:AW38,BA24:BG38,AC39,AW39,BG39,W40:AC108,AQ40:AW108,BA40:BG108,M41:S108,AG41:AM108,M24:S38,M9:S21Cell Value>0textNO
F9:F21Cell Valuebetween 0.81 and 100%textNO
F9:F21Cell Valuebetween 0 and 0.8textNO
F9:F21Cell Value>100%textNO
M11:S11Expression=OR(M6=M11,M6>I11, M6<J11,M6=I11)textNO
Cells with Data Validation
CellAllowCriteria
B9:B21List=$B$214:$B$221
M9:O9Any value
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Jedi Master said:
Foreman Project Tracker below: feeds PM Project Tracker
New Bid Master.xlsm
BCDEFMNOPQR
5Jun 24, 2024
6Labor Tracking242526272829
7PositionEmployee NameBudget HrsHrs Used% usedMTWTFS
8
9LaborerJohnny1100105395.73%889281000
10ForemanTimmy2754215.27%810888
11LaborerTommy110080.73%8
12LaborerSally110080.73%8
13ForemanTrent2755018.18%1010101010
14LaborerLisa110000.00%
15Bert 0 
16OperatorSam010 10
17 0 
18 0 
19 0 
20 0 
21 0 
Foreman Project Tracker
Cell Formulas
RangeFormula
M5M5=M6
M6M6=Project_Start
N6:R6N6=M6+1
M7:R7M7=LEFT(TEXT(M6,"ddd"),1)
D9D9=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
E9:E21E9=SUM(M9:S9,W9:AC9,AG9:AM9,AQ9:AW9,BA9:BG9,BK9:BQ9,BU9:CA9,CE9:CK9,CO9:CU9,CY9:DE9,DI9:DO9,DS9:DY9,EC9:EI9,EM9:ES9,EW9:FC9,FG9:FM9,FQ9:FW9,GA9:GG9,GK9:GQ9,GU9:HA9,HE9:HK9,HO9:HU9,HY9:IE9,II9:IO9,IS9:IY9,JC9:JI9,JM9:JS9,JW9:KC9,KG9:KM9,KQ9:KW9,LA9:LG9,LK9:LQ9,LU9:MA9,ME9:MK9,MO9:MU9,MY9:NE9,NI9:NO9,NS9:NY9,OC9:OI9,OM9:OS9,OW9:PC9,PG9:PM9,PQ9:PW9,QA9:QG9,QK9:QQ9,QU9:RA9,RE9:RK9,RO9:RU9,RY9:SE9,SI9:SO9,SS9:SY9,TC9:TI9,TM9:TS9,TW9:UC9,UG9:UM9)
F9:F16F9=IF(D9="","",IFERROR(E9/D9,""))
D10D10=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D11D11=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D12D12=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D13D13=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D14D14=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D15D15=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D16D16=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D17D17=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
F17:F21F17=IF(D17="","",E17/D17)
D18D18=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D19D19=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D20D20=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
D21D21=IF([@Column1]="","",IFS([@Column1]="Foreman",SUMIF('2-Quote Master'!B30:B39,"Foreman",'2-Quote Master'!Q30:Q39),[@Column1]="Operator",SUMIF('2-Quote Master'!B30:B39,"Operator",'2-Quote Master'!Q30:Q39),[@Column1]="Laborer",SUMIF('2-Quote Master'!B30:B39,"Laborer",'2-Quote Master'!Q30:Q39),[@Column1]="CDL Driver",SUMIF('2-Quote Master'!B30:B39,"CDL Driver",'2-Quote Master'!Q30:Q39),[@Column1]="Safety Manager",SUMIF('2-Quote Master'!B30:B39,"Safety Manager",'2-Quote Master'!Q30:Q39),[@Column1]="Superintendent",SUMIF('2-Quote Master'!B30:B39,"Superintendent",'2-Quote Master'!Q30:Q39),[@Column1]="Project Manager",SUMIF('2-Quote Master'!B30:B39,"Project Manager",'2-Quote Master'!Q30:Q39)))
Named Ranges
NameRefers ToCells
'Foreman Project Tracker'!Project_Start='Foreman Project Tracker'!$I$4M6
Cells with Conditional Formatting
CellConditionCell FormatStop If True
W9:AC21,AG9:AM21,AQ9:AW21,BA9:BG21,W24:AC38,AG24:AM38,AQ24:AW38,BA24:BG38,AC39,AW39,BG39,W40:AC108,AQ40:AW108,BA40:BG108,M41:S108,AG41:AM108,M24:S38,M9:S21Cell Value>0textNO
F9:F21Cell Valuebetween 0.81 and 100%textNO
F9:F21Cell Valuebetween 0 and 0.8textNO
F9:F21Cell Value>100%textNO
M11:S11Expression=OR(M6=M11,M6>I11, M6<J11,M6=I11)textNO
Cells with Data Validation
CellAllowCriteria
B9:B21List=$B$214:$B$221
M9:O9Any value
Click to expand...
Noticed this error on my part from the earlier post and resolved it. Should reference G20, not G420

1719493630416.png
 
Upvote 0
Thanks for that, There was a typo in the formula, it should be
Excel Formula: 
=LET(x,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M9:UM21),IF($G11="","",CHOOSECOLS(FILTER(HSTACK('PM Project Tracker'!B9:C21,x),x>0),2,1,3)))
 
Upvote 1
Fluff said:
Thanks for that, There was a typo in the formula, it should be
Excel Formula: 
=LET(x,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M9:UM21),IF($G11="","",CHOOSECOLS(FILTER(HSTACK('PM Project Tracker'!B9:C21,x),x>0),2,1,3)))
Click to expand...
That did it! Thank you so much, I really appreciate the help. Now Ill have to dissect it and figure out how you're doing what you're doing to replicate in other areas of the form.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
CHOOSECOLS/FILTER/HSTACK question
Replies
2
Views
314
Fluff
Fluff
J
  • Solved
Filter Question
Replies
3
Views
285
Fluff
Fluff
G
  • Question Question
FILTER formula giving #N/A
Replies
1
Views
310
hagia_sofia
hagia_sofia
K
  • Solved
XLookup Question!
Replies
3
Views
202
AhoyNC
A
R
  • Question Question
Sumifs with nested Xlookup-- Adding Indirect to perform calculation across multiple sheets
Replies
1
Views
500
Eric W
Eric W

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top