Filter/Xlookup error

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
Can you post your original formula?
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
The formula you had after removing the 2nd IF function.
 
Upvote 0
The formula you had after removing the 2nd IF function.
=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
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.
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
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
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.
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
Thanks for that. (y)
Can you also supply the data for the PM Project Tracker sheet.
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

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

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