VSTACK Causing "FALSE"

[thewiseguy]

Hi All - any ideas on what is happening in A4?

BDF.Sau.Ext-08232023.v2023.6 - WIP.xlsm
	A		C	D	E	F	G
1	Mfg/Item		Item Type	Manufacturer	Vendor	Part #	Quantity
2	T1 320W LED Area Light [WS-MCCT]		Fixture	T1 LIGHTING		T1-SALED/MW320-MCCT	13
3	T1 Arm Extension [BR]		Accessory	T1 LIGHTING		AR	13
4	FALSE
5	UNITEDRENTALS Boom, Articulated, 30-39' Diesel Week		Rental	United Rentals		Boom/39ft/Week	1
6
Full BOM (prd.mat.ren.)

 

[thewiseguy]

Thank you again!

 

[Fluff]

You're welcome.

 

[thewiseguy]

v2023.10 .xlsm
	B
5	Mfg/Item
6	#REF!
Shipping (BOM)

Cell Formulas
Range	Formula
B6	B6	=LET(v,VSTACK('ECM (BOM)'!B6#,'Accessories (BOM)'!B6#,'Controls (BOM)'!B6#,'Materials (BOM)'!B6#,'Rental Equipment (BOM)'!B6#),FILTER(v,v<>""))

Any ideas on why this is giving me a cell ref error?

 

[Fluff]

One of the ranges contains a #ref error.

 

[thewiseguy]

should i actually see the #ref error in the cell? I'm looking at all the ranges and do not see anything.

 

[Fluff]

Yes you should, another possibility is that one of the ranges isn't a spill range.

 

[thewiseguy]

Yes i was looking for the spill also. i cant find anything?

v2023.10 .xlsm
	B
5	Mfg/Item
6	MAXLITE 8W LED A-Lamp [27K-E26]
7	T1 LED Wall Pack HW [120W-MCCT]
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
ECM (BOM)

Cell Formulas
Range	Formula
B6:B7	B6	=SORT(UNIQUE(FILTER(Input!W7:W506,(Input!W7:W506<>"")*(Input!W7:W506<>0))))
Dynamic array formulas.

v2023.10 .xlsm
	B
5	Mfg/Item
6	MAXLITE Battery backup for Type C Linears
7	MAXLITE External Adapter Round, White
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Accessories (BOM)

Cell Formulas
Range	Formula
B6:B7	B6	=LET(sh,VSTACK(Input!Z7:Z505,Input!AC7:AC505),SORT(UNIQUE(FILTER(sh,(sh <>"")*(sh<>0),""))))
Dynamic array formulas.

v2023.10 .xlsm
	B
5	Mfg/Item
6	MAXLITE Network Node - Rectangular White
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Controls (BOM)

Cell Formulas
Range	Formula
B6	B6	=SORT(UNIQUE(FILTER(Input!AF7:AF506,(Input!AG7:AG506<>"")*(Input!AF7:AF506<>0))))

v2023.10 .xlsm
	B
5	Mfg/Item
6	TBD MC Armored Cable 12/2
7	TBD Tan Wirenuts
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Materials (BOM)

Cell Formulas
Range	Formula
B6:B7	B6	=LET(sh,VSTACK(Input!AI7:AI505,Input!AL7:AL505),SORT(UNIQUE(FILTER(sh,(sh <>"")*(sh<>0),""))))
Dynamic array formulas.

v2023.10 .xlsm
	B
5	Mfg/Item
6	UNITEDRENTALS Boom, Articulated, 30-39' Diesel Day
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Rental Equipment (BOM)

 

[Fluff]

When you enter/edit the formule & then confirm it do you get a pop-up asking for a file?

 

[thewiseguy]

i do not. i even went to each worksheet and selected the cell, as opposed to typing it in for this reason.

 

[Fluff]

In that case I don't know.