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
Fluff said:
Can you post your original formula?
Click to expand...
Please explain further, original formula for what? If you are referencing a formula to bring in the hours in column "I" associated with the name in column G, I never had one. The formula I posted above is what I have come up with. While it does not filter as columns G and H (Not sure if there is a way to filter the array based on multiple criteria), it does bring in the array value associated with the filtered columns of G and H, I just had to paste that formula in each cell down the column and change the associated cell # in column G within each paste.
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
The formula you had after removing the 2nd IF function.
 
Upvote 0
Fluff said:
The formula you had after removing the 2nd IF function.
Click to expand...
=IF($G11="","",FILTER('PM Project Tracker'!C9:C21,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M9:UM21)>0))

I have this formula in G15 and H15 to pull Name and Position from the Tracker
 
Upvote 0
Thanks for that, how about
Excel Formula: 
=LET(x,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M9:UM21),IF($G11="","",CHOOSECOLS(FILTER(VSTACK('PM Project Tracker'!B9:C21,x),x>0),2,1,3)))
This will return all 3 cols.
 
Upvote 0
Fluff said:
Thanks for that, how about
Excel Formula: 
=LET(x,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M9:UM21),IF($G11="","",CHOOSECOLS(FILTER(VSTACK('PM Project Tracker'!B9:C21,x),x>0),2,1,3)))
This will return all 3 cols.
Click to expand...
I am getting a #VALUE! error.
1719487644677.png

1719487670426.png

1719487692889.png

1719487710278.png

I would love to see this work and understand the functionality of the unfamiliar functions you are using. It may be fair to mention that in this case, the PM Project Tracker is an exact replica of the Foreman Project Tracker. Every cell has a formula in it in the PM project Tracker that = the same cell in the foreman project tracker. Not sure if that could be causing the value error above, but it seems to be finding all the correct info scrolling through the evaluation. I tried to install the add in for xl2bb to send you a sheet if that would help, but the add in failed with an error of "This file type is not supported in protected view". Also my workbook is file type .xlsm. What can I share with you to help solve?
 
Upvote 0
Fluff said:
Have a look here XL2BB Icons greyed out
Click to expand...
New Bid Master.xlsm
GHIJKLMNOPQRSTU
116/24/2024← Work Date
12LaborEquipmentMaterialsDaily Notes
13
14NamePositionHrsCategoryIDTypeQTY Advise the Asset Owner and the Fuel Cell Director of Operations, in advance, of any maintenance activities that needed but are not prescribed in a Work Order. This is to ensure accuracy of the labor standards and to facilitate updates to the Maintenance Plan.
15JohnnyLaborer8Bid Rental D6T Dozer w/winch (Komatsu D65, JD850 LGP, or equivalent)R86865 Dozer D5/D3Fence, Safety 4' x 100'25
16TimmyForeman8Bid Rental Excavator (CAT 308, Kubota KX080, or equivalent)EX601 Excavator, Kubota KX080Test10
17TrentForeman10 
18SamOperator10 
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
MYR Daily Report
Cell Formulas
RangeFormula
J11J11=IF(G11="","← Enter Work Date 'MM/DD/YYYY'","← Work Date")
Q14Q14=IF(ISBLANK($G11),"",FILTER('PM Project Tracker'!M110:UM110,'PM Project Tracker'!M4:UM4=$G11))
G15:G18G15=IF($G11="","",FILTER('PM Project Tracker'!C9:C21,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M9:UM21)>0))
H15:H18H15=IF($G11="","",FILTER('PM Project Tracker'!B9:B21,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M9:UM21)>0))
K15:K16K15=IF($G11="","",FILTER('PM Project Tracker'!B24:B37,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M24:UM37)>0))
L15:L16L15=IF($G11="","",FILTER('PM Project Tracker'!C24:C37,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M24:UM37)>0))
N15:N16N15=IF($G11="","",FILTER('PM Project Tracker'!B41:B55,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M41:UM55)>0))
I15I15=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G15)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G15)*('PM Project Tracker'!M4:UM4=$G11))))
I16I16=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G16)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G16)*('PM Project Tracker'!M4:UM4=$G11))))
I17I17=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G17)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G17)*('PM Project Tracker'!M4:UM4=$G11))))
I18I18=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G18)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G18)*('PM Project Tracker'!M4:UM4=$G11))))
I19I19=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G19)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G19)*('PM Project Tracker'!M4:UM4=$G11))))
I20I20=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G20)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G420)*('PM Project Tracker'!M4:UM4=$G11))))
I21I21=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G21)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G21)*('PM Project Tracker'!M4:UM4=$G11))))
I22I22=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G22)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G22)*('PM Project Tracker'!M4:UM4=$G11))))
I23I23=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G23)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G23)*('PM Project Tracker'!M4:UM4=$G11))))
I24I24=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G24)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G24)*('PM Project Tracker'!M4:UM4=$G11))))
I25I25=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G25)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G25)*('PM Project Tracker'!M4:UM4=$G11))))
I26I26=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G26)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G26)*('PM Project Tracker'!M4:UM4=$G11))))
I27I27=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G27)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G27)*('PM Project Tracker'!M4:UM4=$G11))))
I28I28=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G28)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G28)*('PM Project Tracker'!M4:UM4=$G11))))
O15O15=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N15)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N15)*('PM Project Tracker'!M4:UM4=$G11))))
O16O16=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N16)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N16)*('PM Project Tracker'!M4:UM4=$G11))))
O17O17=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N17)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N17)*('PM Project Tracker'!M4:UM4=$G11))))
O18O18=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N18)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N18)*('PM Project Tracker'!M4:UM4=$G11))))
O19O19=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N19)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N19)*('PM Project Tracker'!M4:UM4=$G11))))
O20O20=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N20)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N20)*('PM Project Tracker'!M4:UM4=$G11))))
O21O21=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N21)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N21)*('PM Project Tracker'!M4:UM4=$G11))))
O22O22=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N22)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N22)*('PM Project Tracker'!M4:UM4=$G11))))
O23O23=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N23)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N23)*('PM Project Tracker'!M4:UM4=$G11))))
O24O24=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N24)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N24)*('PM Project Tracker'!M4:UM4=$G11))))
O25O25=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N25)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N25)*('PM Project Tracker'!M4:UM4=$G11))))
O26O26=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N26)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N26)*('PM Project Tracker'!M4:UM4=$G11))))
O27O27=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N27)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N27)*('PM Project Tracker'!M4:UM4=$G11))))
O28O28=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N28)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N28)*('PM Project Tracker'!M4:UM4=$G11))))
Dynamic array formulas.
 
Upvote 0
Jedi Master said:
New Bid Master.xlsm
GHIJKLMNOPQRSTU
116/24/2024← Work Date
12LaborEquipmentMaterialsDaily Notes
13
14NamePositionHrsCategoryIDTypeQTY Advise the Asset Owner and the Fuel Cell Director of Operations, in advance, of any maintenance activities that needed but are not prescribed in a Work Order. This is to ensure accuracy of the labor standards and to facilitate updates to the Maintenance Plan.
15JohnnyLaborer8Bid Rental D6T Dozer w/winch (Komatsu D65, JD850 LGP, or equivalent)R86865 Dozer D5/D3Fence, Safety 4' x 100'25
16TimmyForeman8Bid Rental Excavator (CAT 308, Kubota KX080, or equivalent)EX601 Excavator, Kubota KX080Test10
17TrentForeman10 
18SamOperator10 
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
MYR Daily Report
Cell Formulas
RangeFormula
J11J11=IF(G11="","← Enter Work Date 'MM/DD/YYYY'","← Work Date")
Q14Q14=IF(ISBLANK($G11),"",FILTER('PM Project Tracker'!M110:UM110,'PM Project Tracker'!M4:UM4=$G11))
G15:G18G15=IF($G11="","",FILTER('PM Project Tracker'!C9:C21,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M9:UM21)>0))
H15:H18H15=IF($G11="","",FILTER('PM Project Tracker'!B9:B21,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M9:UM21)>0))
K15:K16K15=IF($G11="","",FILTER('PM Project Tracker'!B24:B37,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M24:UM37)>0))
L15:L16L15=IF($G11="","",FILTER('PM Project Tracker'!C24:C37,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M24:UM37)>0))
N15:N16N15=IF($G11="","",FILTER('PM Project Tracker'!B41:B55,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M41:UM55)>0))
I15I15=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G15)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G15)*('PM Project Tracker'!M4:UM4=$G11))))
I16I16=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G16)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G16)*('PM Project Tracker'!M4:UM4=$G11))))
I17I17=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G17)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G17)*('PM Project Tracker'!M4:UM4=$G11))))
I18I18=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G18)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G18)*('PM Project Tracker'!M4:UM4=$G11))))
I19I19=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G19)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G19)*('PM Project Tracker'!M4:UM4=$G11))))
I20I20=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G20)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G420)*('PM Project Tracker'!M4:UM4=$G11))))
I21I21=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G21)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G21)*('PM Project Tracker'!M4:UM4=$G11))))
I22I22=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G22)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G22)*('PM Project Tracker'!M4:UM4=$G11))))
I23I23=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G23)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G23)*('PM Project Tracker'!M4:UM4=$G11))))
I24I24=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G24)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G24)*('PM Project Tracker'!M4:UM4=$G11))))
I25I25=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G25)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G25)*('PM Project Tracker'!M4:UM4=$G11))))
I26I26=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G26)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G26)*('PM Project Tracker'!M4:UM4=$G11))))
I27I27=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G27)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G27)*('PM Project Tracker'!M4:UM4=$G11))))
I28I28=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G28)*('PM Project Tracker'!M4:UM4=G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M9:UM21)*('PM Project Tracker'!C9:C21=G28)*('PM Project Tracker'!M4:UM4=$G11))))
O15O15=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N15)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N15)*('PM Project Tracker'!M4:UM4=$G11))))
O16O16=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N16)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N16)*('PM Project Tracker'!M4:UM4=$G11))))
O17O17=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N17)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N17)*('PM Project Tracker'!M4:UM4=$G11))))
O18O18=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N18)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N18)*('PM Project Tracker'!M4:UM4=$G11))))
O19O19=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N19)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N19)*('PM Project Tracker'!M4:UM4=$G11))))
O20O20=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N20)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N20)*('PM Project Tracker'!M4:UM4=$G11))))
O21O21=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N21)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N21)*('PM Project Tracker'!M4:UM4=$G11))))
O22O22=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N22)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N22)*('PM Project Tracker'!M4:UM4=$G11))))
O23O23=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N23)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N23)*('PM Project Tracker'!M4:UM4=$G11))))
O24O24=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N24)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N24)*('PM Project Tracker'!M4:UM4=$G11))))
O25O25=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N25)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N25)*('PM Project Tracker'!M4:UM4=$G11))))
O26O26=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N26)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N26)*('PM Project Tracker'!M4:UM4=$G11))))
O27O27=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N27)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N27)*('PM Project Tracker'!M4:UM4=$G11))))
O28O28=IF(ISBLANK($G11),"",IF(SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N28)*('PM Project Tracker'!M4:UM4=$G11))=0,"",SUMPRODUCT(('PM Project Tracker'!M41:UM55)*('PM Project Tracker'!B41:B55=N28)*('PM Project Tracker'!M4:UM4=$G11))))
Dynamic array formulas.
Click to expand...
Not sure if the mini sheet will help without the other referenced workbook sheets?
 
Upvote 0
Thanks for that. (y)
Can you also supply the data for the PM Project Tracker sheet.
 
Upvote 0
Fluff said:
Thanks for that. (y)
Can you also supply the data for the PM Project Tracker sheet.
Click to expand...
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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