Vstack, Chooserows & Dollar

[Nathan Asius]

Hello, I'm hoping someone can help me with this. I think I almost have it.

I am pulling a list of custom user-selected items from a worksheet using a VSTACK function. This is to create a Purchase Order. I have multiple named ranges that are also allow for filtering out blank rows. I this way, no matter how few options, or how many options are selected to be ordered, the list populated by VSTACK appears on another worksheet with no black rows or gaps in the data.

=IF(PanelTotal="","",VSTACK(FILTER(PanelSubPriceToPO,PanelSubPriceToPO<>""))," ")

The Range PanelSubPriceToPO from another worksheet is a single column. The Top row is a header that contains text and proves useful for me to have in the VSTACK. The rest of the column are 16 rows of cells that will contain numbers. VSTACK is very limited in formatting.

However, I managed to find that when I nest the DOLLAR function in the formula, all the numbers convert into the currency format when the result of the VSTACK is displayed.
=IF(PanelTotal="","",VSTACK((DOLLAR(FILTER(PanelSubPriceToPO,PanelSubPriceToPO<>"")))))
The first row in the VSTACK then results to be VALUE# error because the DOLLAR function is applying to the text to it as well.

To solve this, I aimed to separate the first row in the range using the CHOOSEROWS(PanelSubPriceToPO,1) to take the top row and separate it, keeping it as the header in the VSTACK display. The remaining 16 rows with numbers all convert to currency.

=IF(PanelTotal="","",VSTACK(CHOOSEROWS(PanelSubPriceToPO,1),(DOLLAR(FILTER(PanelSubPriceToPO,PanelSubPriceToPO<>"")))))

This would work beautifully with the one small snag that the row immediately under the header that I managed to separate, gives me a VALUE# error again. The rest of the cells in the named range are all there but have dropped one row lower. (See image)
No matter where I attempt to place the Chooserows Argument, the FILTER, or the DOLLAR arguments, nor my trial and error with the parentheses, I can not manage to make this work the way I would like.

Is there a problem with my syntax I've overlooked? It seems I'm so close.
Or can I not do what I'm aiming to do with the VSTACK? If so, please suggest a different function that may work better.

Nathan

 

[StephenCrump]

Try: =IF(PanelTotal="","",LET(p,PanelSubPriceToPO,f,FILTER(p,p<>""),IFERROR(DOLLAR(f),f)))

But are you sure you want to use the DOLLAR function, rather than formatting the cells?

DOLLAR() converts to text, hence for example:

	A
1	$10.00
2	$20.00
3
4	0
Sheet1

Cell Formulas
Range	Formula
A1	A1	=DOLLAR(10,2)
A2	A2	=DOLLAR(20,2)
A4	A4	=SUM(A1:A2)

 

[Nathan Asius]

That Formula works perfectly. Thank You Stephen.
In other areas where I need to use it, where I'm 'stacking' a combinations or multiple arrays, (not just the single array one I used in the example), It won't work.
Can you suggest how to nest this 'Let(p' function into a VSTACK Function? Do I simply repeat the same arguments and substitute different arrays (my named ranges)?

 

[Cubist]

Maybe this

=LET(v,TOCOL(VSTACK(namedRng1,namedRng2,namedRng3),3),DOLLAR(v,2))

 

[Nathan Asius]

Stephen, in answer to your previous question whether I'm sure to use DOLLAR function rather than in-cell formatting:
When using VSTACK, text formatting can be activated but that formatting stays in that cell pinned to the worksheet. The array that 'floats' or spills over it may differ in content and the number of entries. So if my array only has two items, then the 3rd or fourth cell down in the column I might not want it converted to to a currency format. It may be text, and not a number.

 

[Nathan Asius]

This is the nested VSTACK I'm attempting to make work.

The first argument with the named range 'PanelCutLengthstoPO', is a range with numbers that are dimensions formatted in feet and inches. So I don't want this array to be shown in currency format using DOLLAR. The other three arrays I want to populate beneath the first with a blank row in between separating each. Each of these three arrays are subtotal amounts so they each need to be in the currency format using DOLLAR.

=IF(PanelTotal="","",VSTACK(FILTER(PanelCutLengthsToPO,PanelCutLengthsToPO<>"")," ",LET(sts,StdTrimSubtotlToPO,fs,FILTER(sts,sts<>""),IFERROR(DOLLAR(fs),fs))," ",(cts,CustomTrimSubtotalToPO,fc,FILTER(fc,fc<>""),IFERROR(DOLLAR((fc),fc))," ",(hs,HASubtotalToPO,fh,FILTER(hs,hs<>""),IFERROR(fh),fh)),""))

Either I have a problem with syntax, or the nested formula won't work with a LET. I continue to get the error: "You can't include a parameter to a LET function after defining it's calculation"

Thanks,Nathan

 

[Cubist]

Have you tried post #4? If you did, what didn't work?

 

[Nathan Asius]

So If I combine my first formula, and Cubist's VSTACK suggestion, it seems to get close to what's needed here.

=IF(PanelTotal="","",VSTACK(FILTER(PanelCutLengthsToPO,PanelCutLengthsToPO<>"")," ",LET(v,TOCOL(VSTACK(StdTrimSubtotlToPO," ",CustomTrimSubtotalToPO," ",HASubtotalToPO," "),3),DOLLAR(v,2))))

When using the above, I get VALUE# errors displaying in each of the three arrays in the nested VSTACK. When I attempt to attach the IFERROR before the DOLLAR, the formula either fails, or I get prices displayed with lengthy trailing zeros after the decimal place.

The VALUE# errors are the text Headers having applied DOLLAR to it.

 

[Nathan Asius]

Have you tried post #4? If you did, what didn't work?

If you read my first post talking about why I attempted to use CHOOSE(ROWS, I needed to separate the first row in each array because it's text and can't have the Currency Format. So I tried your formula and the DOLLAR Function gets applied to the entire array, producing the #VALUE! Error. For some reason it also doesn't filter out blank rows.

 

[StephenCrump]

So If I combine my first formula, and Cubist's VSTACK suggestion, it seems to get close to what's needed here.

=IF(PanelTotal="","",VSTACK(FILTER(PanelCutLengthsToPO,PanelCutLengthsToPO<>"")," ",LET(v,TOCOL(VSTACK(StdTrimSubtotlToPO," ",CustomTrimSubtotalToPO," ",HASubtotalToPO," "),3),DOLLAR(v,2))))

Yes, very close:

	A	B	C	D
1	PanelTotal
2	Some value
3
4	PanelCutLengthsToPO	StdTrimSubtotlToPO	CustomTrimSubtotalToPO	HASubtotalToPO
5	a	$2.00	$2.00	$0.50
6	b	$2.50	$3.00	$1.50
7	c	$1.75	$5.00	$2.50
8		$4.50		$3.50
9	d			$4.50
10
11	e
12			PanelCutLengthsToPO
13			a
14			b
15			c
16			d
17			e
18
19			StdTrimSubtotlToPO
20			$2.00
21			$2.50
22			$1.75
23			$4.50
24
25			CustomTrimSubtotalToPO
26			$2.00
27			$3.00
28			$5.00
29
30			HASubtotalToPO
31			$0.50
32			$1.50
33			$2.50
34			$3.50
35			$4.50
36
Sheet1

Cell Formulas
Range	Formula
C12:C36	C12	=IF(PanelTotal="","",VSTACK(FILTER(PanelCutLengthsToPO,PanelCutLengthsToPO<>"")," ",LET(v,VSTACK(StdTrimSubtotlToPO," ",CustomTrimSubtotalToPO," ",HASubtotalToPO," "),IFERROR(DOLLAR(v),v))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
CustomTrimSubtotalToPO	=Sheet2!$C$4:$C$7	C12
HASubtotalToPO	=Sheet2!$D$4:$D$9	C12
PanelCutLengthsToPO	=Sheet2!$A$4:$A$11	C12
PanelTotal	=Sheet2!$A$2	C12
StdTrimSubtotlToPO	=Sheet2!$B$4:$B$8	C12