Nathan Asius
New Member
- Joined
- Jan 15, 2024
- Messages
- 41
- Office Version
- 365
- Platform
- Windows
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
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