LET/VSTACK Help

[Jedi Master]

I am trying to LET(X,VSTACK a column (First mini sheet, column B31:B59) based on a value greater (2nd mini sheet) than 0 in columns M4:UM4 based on a selected date. Date will be input in a different sheet (Daily Report) in cell E3.

FE Cadiz St3375-3317 Access.xlsm
	B	C	D	E	F	G	H	I	J
30	Equipment Category	Equip ID	Budget Days	Days Used	% used	Budget Billing $	Cost $ used	GM %	Budget Cost $
31	Bid Rental Crew Truck	RTK837 7WWP3D 2024 RAM B25C SDB4 BLACK	15	3	20.00%	$1,725.00	$255.00	85.22%	$1,275.00
32	Bid Rental Skidloader (Kubota SVL 95/97, CAT 299, CAT 289)	SK705 Trackloader, Kubota SVL97	15	3	20.00%	$6,750.00	$627.00	90.71%	$3,135.00
33	Bid Rental Excavator (CAT 308, Kubota KX080, or equivalent)	EX601 Excavator, Kubota KX080	15	3	20.00%	$7,500.00	$660.00	91.20%	$3,300.00
34	Bid Rental PORTA-JOHN (Pair)		15	0	0.00%	$525.00	$75.00	85.71%	$375.00
35	Bid Rental D5 Dozer (Komatsu D39, or equivalent)	EX607 Dozer, Komatsu D51 PXi	15	3	20.00%	$8,250.00	$867.00	89.49%	$4,335.00
36	Bid Rental SXS/UTV		15	0	0.00%	$1,950.00	$0.00	100.00%	$885.00
37	Bid Rental 4x4 Stakebed Truck (F550/5500)	R83871 Truck, Cat F550 Stake Bed	15	3	20.00%	$2,325.00	$345.00	85.16%	$1,725.00
38	Bid Rental Roller (CS54/56, or equivalent)		10	1	10.00%	$5,500.00	$279.00	94.93%	$2,790.00
39	Bid Rental Utility Trailer		10	0	0.00%	$750.00	$0.00	100.00%	$250.00
40				0
41				0
42				0
43				0
44				0
45				0
46				0
47				0
48				0
49				0
50				0
51				0
52				0
53				0
54				0
55	Semi Truck Peterbuilt	TK102 Truck, Peterbilt 389 Semi	4	2	50.00%	$0.00	$500.00	#DIV/0!	$1,000.00
56	Triaxle Trailer Detach	TR401 Trailer, Talbert lowboy tri axle	4	2	50.00%	$0.00	$0.00	#DIV/0!	$0.00
57	hydrema dump	r88884		1
58				0
59				0
PM Project Tracker

Cell Formulas
Range	Formula
B31:B59	B31	=IF(ForemanProjectTracker[@Column1]="","",ForemanProjectTracker[@Column1])
C31:C59	C31	=IF(ForemanProjectTracker[@Column2]="","",ForemanProjectTracker[@Column2])
D31:D54	D31	=IF(ForemanProjectTracker[@Column3]="","",ForemanProjectTracker[@Column3])
E31:E59	E31	=COUNTIFS(M31:UW31,">0")
F31:F59	F31	=IF(D31="","",E31/D31)
G31:G54	G31	=IF('2-Quote Master'!H50="","",'2-Quote Master'!H50)
H31:H54	H31	=IF([@Column6]="","",SUM(COUNTIFS(PMProjectTracker[@[Column12]:[Column558]],{">0","*"}))*('2-Quote Master'!V50/[@Column3]))
I31:I59	I31	=IF(G31="","",1-H31/G31)
J31	J31	=IF('2-Quote Master'!H50="","",'2-Quote Master'!V50)
J32	J32	=IF('2-Quote Master'!H50="","",'2-Quote Master'!V51)
J33	J33	=IF('2-Quote Master'!H50="","",'2-Quote Master'!V52)
J34	J34	=IF('2-Quote Master'!H50="","",'2-Quote Master'!V53)
J35	J35	=IF('2-Quote Master'!H50="","",'2-Quote Master'!V54)
J36	J36	=IF('2-Quote Master'!H50="","",'2-Quote Master'!V55)
J37	J37	=IF('2-Quote Master'!H50="","",'2-Quote Master'!V56)
J38	J38	=IF('2-Quote Master'!H50="","",'2-Quote Master'!V57)
J39:J54	J39	=IF('2-Quote Master'!H50="","",'2-Quote Master'!V58)
H55:H59	H55	=IF([@Column6]="","",SUM(COUNTIFS(PMProjectTracker[@[Column12]:[Column558]],{">0","*"}))*([@Column9]/[@Column3]))
J55	J55	=250*4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F31:F59,F63:F103	Cell Value	between 0.81 and 100%	text	NO
F31:F59,F63:F103	Cell Value	<0.8	text	NO
F31:F59,F63:F103	Cell Value	>100%	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D55:D59	Any value
G55:G59	Any value
J55:J59	Any value

FE Cadiz St3375-3317 Access.xlsm
	M	N	O	P	Q	R	S
4	8/5/2024	8/6/2024	8/7/2024	8/8/2024	8/9/2024	8/10/2024	8/11/2024
5	Aug 5, 2024
6	5	6	7	8	9	10	11
7	M	T	W	T	F	S	S
8
9	10	10	10	0	0	0	0
10	10	10	10	0	0	0	0
11	10	10	10	0	0	0	0
12	10	10	10	0	0	0	0
13	10	10	10	0	0	0	0
14	13.5	0	6	0	0	0	0
15	0	0	0	0	0	0	0
16	0	0	0	0	0	0	0
17	0	0	0	0	0	0	0
18	0	0	0	0	0	0	0
19	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0
21	0	0	0	0	0	0	0
22	0	0	0	0	0	0	0
23	0	0	0	0	0	0	0
24	0	0	0	0	0	0	0
25	0	0	0	0	0	0	0
26	0	0	0	0	0	0	0
27	0	0	0	0	0	0	0
28	0	0	0	0	0	0	0
PM Project Tracker

Cell Formulas
Range	Formula
N4	N4	=[@Column12]+1
O4	O4	=[@Column13]+1
P4	P4	=[@Column14]+1
Q4	Q4	=[@Column15]+1
R4	R4	=[@Column16]+1
S4	S4	=[@Column17]+1
M4,M6	M4	=Project_Start
M5	M5	=M6
N6:S6	N6	=M6+1
M7:S7	M7	=LEFT(TEXT(M6,"ddd"),1)
M9:M28	M9	=ForemanProjectTracker[@Column12]
N9:N28	N9	=ForemanProjectTracker[@Column13]
O9:O28	O9	=ForemanProjectTracker[@Column14]
P9:P28	P9	=ForemanProjectTracker[@Column15]
Q9:Q28	Q9	=ForemanProjectTracker[@Column16]
R9:R28	R9	=ForemanProjectTracker[@Column17]
S9:S28	S9	=ForemanProjectTracker[@Column18]

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
OC60:OI62,OC106:OH156,OC104:OI105,M9:S28,W9:AC28,AG9:AM28,AQ9:AW28,BA9:BG28,M31:S59,W31:AC59,AG31:AM59,AQ31:AW59,BA31:BG59,OC31:OH59,M63:S156,W61:AC156,AG63:AM156,AQ61:AW156,BA61:BG156,OC63:OH103	Cell Value	>0	text	NO
M11:S11	Expression	=OR(M6=M11,M6>I11, M6<J11,M6=I11)	text	NO

 

[Jedi Master]

Above 2nd mini sheet range was wrong, below is the correct

FE Cadiz St3375-3317 Access.xlsm
	M	N	O	P	Q	R	S
31	4	4	4	0	0	0	0
32	4	3	7	0	0	0	0
33	3	1	2	0	0	0	0
34	x	x	x	0	0	0	0
35	6	8	8	0	0	0	0
36	0	0	0	0	0	0	0
37	4	2	2	0	0	0	0
38	0	0	3	0	0	0	0
39	0	0	0	0	0	0	0
40	0	0	0	0	0	0	0
41	0	0	0	0	0	0	0
42	0	0	0	0	0	0	0
43	0	0	0	0	0	0	0
44	0	0	0	0	0	0	0
45	0	0	0	0	0	0	0
46	0	0	0	0	0	0	0
47	0	0	0	0	0	0	0
48	0	0	0	0	0	0	0
49	0	0	0	0	0	0	0
50	0	0	0	0	0	0	0
51	0	0	0	0	0	0	0
52	0	0	0	0	0	0	0
53	0	0	0	0	0	0	0
54	0	0	0	0	0	0	0
55	12	0	6	0	0	0	0
56	12	0	6	0	0	0	0
57	0	0	4	0	0	0	0
58	0	0	0	0	0	0	0
59	0	0	0	0	0	0	0
PM Project Tracker

Cell Formulas
Range	Formula
M31:M59	M31	=ForemanProjectTracker[@Column12]
N31:N59	N31	=ForemanProjectTracker[@Column13]
O31:O59	O31	=ForemanProjectTracker[@Column14]
P31:P59	P31	=ForemanProjectTracker[@Column15]
Q31:Q59	Q31	=ForemanProjectTracker[@Column16]
R31:R59	R31	=ForemanProjectTracker[@Column17]
S31:S59	S31	=ForemanProjectTracker[@Column18]

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
OC60:OI62,OC106:OH156,OC104:OI105,M9:S28,W9:AC28,AG9:AM28,AQ9:AW28,BA9:BG28,M31:S59,W31:AC59,AG31:AM59,AQ31:AW59,BA31:BG59,OC31:OH59,M63:S156,W61:AC156,AG63:AM156,AQ61:AW156,BA61:BG156,OC63:OH103	Cell Value	>0	text	NO

 

[Jedi Master]

This is the solution:

=LET(x,XLOOKUP($E3,'PM Project Tracker'!M4:UM4,'PM Project Tracker'!M31:UM59),IF($E3="","",CHOOSECOLS(FILTER(HSTACK('PM Project Tracker'!B31:B59,x),x>0),1)))