Filter/Xlookup error

Jedi Master · Jun 25, 2024

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:

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

Jedi Master · Jun 26, 2024

Fluff said:
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.

Fluff · Jun 26, 2024

The formula you had after removing the 2nd IF function.

Jedi Master · Jun 26, 2024

Fluff said:
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

Fluff · Jun 26, 2024

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.

Jedi Master · Jun 27, 2024

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.

I am getting a #VALUE! error.

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?

Fluff · Jun 27, 2024

Jedi Master said:
the add in failed with an error of "This file type is not supported in protected view"

Have a look here XL2BB Icons greyed out

Jedi Master · Jun 27, 2024

Fluff said:
Have a look here XL2BB Icons greyed out

New Bid Master.xlsm

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

11

6/24/2024

← Work Date

12

Labor

Equipment

Materials

Daily Notes

13

14

Name

Position

Hrs

Category

ID

Type

QTY

 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.

15

Johnny

Laborer

8

Bid Rental D6T Dozer w/winch (Komatsu D65, JD850 LGP, or equivalent)

R86865 Dozer D5/D3

Fence, Safety 4' x 100'

25

16

Timmy

Foreman

8

Bid Rental Excavator (CAT 308, Kubota KX080, or equivalent)

EX601 Excavator, Kubota KX080

Test

10

17

Trent

Foreman

10

18

Sam

Operator

10

19

20

21

22

23

24

25

26

27

28

MYR Daily Report

Cell Formulas
Range	Formula
J11	J11	=IF(G11="","← Enter Work Date 'MM/DD/YYYY'","← Work Date")
Q14	Q14	=IF(ISBLANK($G11),"",FILTER('PM Project Tracker'!M110:UM110,'PM Project Tracker'!M4:UM4=$G11))
G15:G18	G15	=IF($G11="","",FILTER('PM Project Tracker'!C9:C21,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M9:UM21)>0))
H15:H18	H15	=IF($G11="","",FILTER('PM Project Tracker'!B9:B21,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M9:UM21)>0))
K15:K16	K15	=IF($G11="","",FILTER('PM Project Tracker'!B24:B37,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M24:UM37)>0))
L15:L16	L15	=IF($G11="","",FILTER('PM Project Tracker'!C24:C37,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M24:UM37)>0))
N15:N16	N15	=IF($G11="","",FILTER('PM Project Tracker'!B41:B55,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M41:UM55)>0))
I15	I15	=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))))
I16	I16	=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))))
I17	I17	=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))))
I18	I18	=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))))
I19	I19	=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))))
I20	I20	=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))))
I21	I21	=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))))
I22	I22	=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))))
I23	I23	=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))))
I24	I24	=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))))
I25	I25	=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))))
I26	I26	=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))))
I27	I27	=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))))
I28	I28	=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))))
O15	O15	=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))))
O16	O16	=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))))
O17	O17	=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))))
O18	O18	=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))))
O19	O19	=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))))
O20	O20	=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))))
O21	O21	=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))))
O22	O22	=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))))
O23	O23	=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))))
O24	O24	=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))))
O25	O25	=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))))
O26	O26	=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))))
O27	O27	=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))))
O28	O28	=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.

Jedi Master · Jun 27, 2024

Jedi Master said:
New Bid Master.xlsm
G H I J K L M N O P Q R S T U
11 6/24/2024 ← Work Date
12 Labor Equipment Materials Daily Notes
13
14 Name Position Hrs Category ID Type QTY  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.
15 Johnny Laborer 8 Bid Rental D6T Dozer w/winch (Komatsu D65, JD850 LGP, or equivalent) R86865 Dozer D5/D3 Fence, Safety 4' x 100' 25
16 Timmy Foreman 8 Bid Rental Excavator (CAT 308, Kubota KX080, or equivalent) EX601 Excavator, Kubota KX080 Test 10
17 Trent Foreman 10
18 Sam Operator 10
19
20
21
22
23
24
25
26
27
28
MYR Daily Report
Cell Formulas
Range Formula
J11 J11 =IF(G11="","← Enter Work Date 'MM/DD/YYYY'","← Work Date")
Q14 Q14 =IF(ISBLANK($G11),"",FILTER('PM Project Tracker'!M110:UM110,'PM Project Tracker'!M4:UM4=$G11))
G15:G18 G15 =IF($G11="","",FILTER('PM Project Tracker'!C9:C21,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M9:UM21)>0))
H15:H18 H15 =IF($G11="","",FILTER('PM Project Tracker'!B9:B21,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M9:UM21)>0))
K15:K16 K15 =IF($G11="","",FILTER('PM Project Tracker'!B24:B37,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M24:UM37)>0))
L15:L16 L15 =IF($G11="","",FILTER('PM Project Tracker'!C24:C37,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M24:UM37)>0))
N15:N16 N15 =IF($G11="","",FILTER('PM Project Tracker'!B41:B55,XLOOKUP($G11,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M41:UM55)>0))
I15 I15 =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))))
I16 I16 =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))))
I17 I17 =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))))
I18 I18 =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))))
I19 I19 =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))))
I20 I20 =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))))
I21 I21 =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))))
I22 I22 =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))))
I23 I23 =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))))
I24 I24 =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))))
I25 I25 =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))))
I26 I26 =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))))
I27 I27 =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))))
I28 I28 =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))))
O15 O15 =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))))
O16 O16 =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))))
O17 O17 =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))))
O18 O18 =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))))
O19 O19 =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))))
O20 O20 =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))))
O21 O21 =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))))
O22 O22 =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))))
O23 O23 =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))))
O24 O24 =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))))
O25 O25 =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))))
O26 O26 =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))))
O27 O27 =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))))
O28 O28 =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?

Fluff · Jun 27, 2024

Thanks for that.

Can you also supply the data for the PM Project Tracker sheet.

Jedi Master · Jun 27, 2024

Fluff said:
Thanks for that.
Can you also supply the data for the PM Project Tracker sheet.

New Bid Master.xlsm

B

C

9

10

11

12

13

14

15

16

17

18

19

20

21

PM Project Tracker

Cell Formulas
Range	Formula
B9:B21	B9	=IF(ForemanProjectTracker[@Column1]="","",ForemanProjectTracker[@Column1])
C9:C21	C9	=IF(ForemanProjectTracker[@Column2]="","",ForemanProjectTracker[@Column2])

New Bid Master.xlsm

M

9

10

11

12

13

14

15

16

17

18

19

20

21

PM Project Tracker

Cell Formulas
Range	Formula
M9:M21	M9	=ForemanProjectTracker[@Column12]

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop 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:S21	Cell Value	>0	text	NO
M11:S11	Expression	=OR(M6=M11,M6>I11, M6<J11,M6=I11)	text	NO

Filter/Xlookup error

Jedi Master

Board Regular

Attachments

Jedi Master

Board Regular

Excel Facts

Fluff

MrExcel MVP, Moderator

Jedi Master

Board Regular

Fluff

MrExcel MVP, Moderator

Jedi Master

Board Regular

Fluff

MrExcel MVP, Moderator

Jedi Master

Board Regular

Jedi Master

Board Regular

Fluff

MrExcel MVP, Moderator

Jedi Master

Board Regular

Similar threads

Forum statistics

Share this page

Filter/Xlookup error

Board Regular

Attachments

Board Regular

Excel Facts

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

Board Regular

MrExcel MVP, Moderator

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock