Problems getting 5 functions that work individually to work as one.

[fees4steez]

I am using Excel 2016 on Windows 10. This is also my first post, so if I need to provide more info or change something please let me know!

I am building a new order form for our company, and am running into errors. On 5 different sheets are different product categories. Next to those products are a column for each location to input quantity. I am trying to build a formula that will take all the values in those columns and paste it into a cell on the Order form (which would be on the 6th sheet), with a formula to populate the name of the product and cost as well. The key to this formula is that it cannot have blank lines. When I paste each of the five formulas individually, it pulls the correct results. The problem is I cannot get all of them to work together, I either get a #NUM! error or "". These have to be performed as an array formula (Ctrl+Shift+Enter) to work otherwise I get a #VALUE! Error. Through trying IFS, IF, SWITCH, and CHOOSE, I can't get them all to work together. As a test, I populated the first 5 cells in the Array with a unique number to each sheet. The first 5 from the first sheet always populate, but the other ones are either #NUM! or "".

Here are the formulas:

=IF(ROWS(Sheet1!$7:7)<=COUNTA(ARRAY1),INDEX(ARRAY1,SMALL(IF(ARRAY1<>"",ROW(ARRAY1)-MIN(ROW(ARRAY1))+1),ROWS(Juice!$7:7))),"")
=IF(ROWS('Sheet2'!$7:7)<=COUNTA(ARRAY2),INDEX(ARRAY2,SMALL(IF(ARRAY2<>"",ROW(ARRAY2)-MIN(ROW(ARRAY2))+1),ROWS('Sheet2'!$7:7))),"")
=IF(ROWS('Sheet3'!$7:7)<=COUNTA(ARRAY3),INDEX(ARRAY3,SMALL(IF(ARRAY3<>"",ROW(ARRAY3)-MIN(ROW(ARRAY3))+1),ROWS('Sheet3'!$7:7))),"")
=IF(ROWS('Sheet4'!$7:7)<=COUNTA(ARRAY4),INDEX(ARRAY4,SMALL(IF(ARRAY4<>"",ROW(ARRAY4)-MIN(ROW(ARRAY4))+1),ROWS('Sheet4'!$7:7))),"")
=IF(ROWS(Sheet5!$7:7)<=COUNTA(ARRAY5),INDEX(ARRAY5,SMALL(IF(ARRAY5<>"",ROW(ARRAY5)-MIN(ROW(ARRAY5))+1),ROWS(Sheet5!$7:7))),"")

The formulas above populate the quantity of the product. When just one of those formulas is pasted into the cell it works.

=IFERROR(OFFSET(IF(ROWS(Sheet1!$7:7)<=COUNTA(ARRAY1),INDEX(ARRAY1,SMALL(IF(ARRAY1<>"",ROW(ARRAY1)-MIN(ROW(ARRAY1))+1),ROWS(Sheet1!$7:7))),""),,-3),"")
=IFERROR(OFFSET(IF(ROWS('Sheet2'!$7:7)<=COUNTA(ARRAY2),INDEX(ARRAY2,SMALL(IF(ARRAY2<>"",ROW(ARRAY2)-MIN(ROW(ARRAY2))+1),ROWS('Sheet2'!$7:7))),""),,-3),"")
=IFERROR(OFFSET(IF(ROWS('Sheet3'!$7:7)<=COUNTA(ARRAY3),INDEX(ARRAY3,SMALL(IF(ARRAY3<>"",ROW(ARRAY3)-MIN(ROW(ARRAY3))+1),ROWS('Sheet3'!$7:7))),""),,-3),"")
=IFERROR(OFFSET(IF(ROWS('Sheet4'!$7:7)<=COUNTA(ARRAY4),INDEX(ARRAY4,SMALL(IF(ARRAY4<>"",ROW(ARRAY4)-MIN(ROW(ARRAY4))+1),ROWS('Sheet4'!$7:7))),""),,-3),"")
=IFERROR(OFFSET(IF(ROWS(Sheet5!$7:7)<=COUNTA(ARRAY5),INDEX(ARRAY5,SMALL(IF(ARRAY5<>"",ROW(ARRAY5)-MIN(ROW(ARRAY5))+1),ROWS(Sheet5!$7:7))),""),,-3),"")

The formulas above populate the product name.

The first line where you can input quantity (no matter which store) is on the 7th row and 5th column of each sheet. When just one of those formulas is pasted into the cell it works.

I appreciate the help!

 

[fees4steez]

The very first formula I edited did not replace the last value that I needed to take out, due to being our company information. Replace Juice!$7:7 with Sheet1!$7:7. Thanks. I also don't know if you can edit posts or not.

 

[Eric W]

Welcome to the forum.

Would you be open to using VBA? I can picture how to create such a formula, but it would be a monster. Maybe 50% longer than the total of all of those 5 formulas put together. And miserable to maintain. But a UDF to do the same thing would be fairly simple.

Let me know and I'll write something up.


By the way, the forum rules allow you to edit your posts for up to 10 minutes after you post them.

 

[fees4steez]

Yes, absolutely! I lack a lot of knowledge regarding VBA, I essentially just know how to record them and not write, and typically don't know why I get errors when doing them sometimes.

 

[Eric W]

Open a copy of your workbook.
Press Alt-F11 to open the VBA editor.
From the menu, click Insert > Module.
Paste the following code:

Public Function OrderVals(ByVal OutRow As Long, t As Long)
Dim myarrays, OutTab(1000, 2), ctr As Long, i As Long, r As Long

    Application.Volatile
    myarrays = Array("Array1", "Array2", "Array3", "Array4", "Array5")
    
    ctr = 0
    
    For i = 0 To UBound(myarrays)
        For r = 1 To Range(myarrays(i)).Rows.Count
            If Range(myarrays(i)).Cells(r, 1) <> "" Then
                ctr = ctr + 1
                OutTab(ctr, 2) = Range(myarrays(i)).Cells(r, 1)
                OutTab(ctr, 1) = Range(myarrays(i)).Cells(r, 1).Offset(0, -3)
            End If
        Next r
    Next i
    
    OrderVals = ""
    If OutRow > ctr Then Exit Function
    If t <> 1 And t <> 2 Then Exit Function
    OrderVals = OutTab(OutRow, t)
    
End Function

Change the array names to the actual names of your arrays.
Press Alt-Q to exit the editor.
On your Order sheet, enter the formulas like this:

Excel 2012

	A	B
1	Product	Count
2	a1	22
3	c1	8
4	e1	2
5	a2	1
6	c2	2
7	e	4
8	a	1
9	c	2
10	e	3
11	a	1
12	c	2
13	e	3
14	a	1
15	c	2
16	e	3
17

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Order Form​

Worksheet Formulas Cell Formula A2 =ordervals(ROWS($A$2:$A2),1) B2 =ordervals(ROWS($A$2:$A2),2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

I use ROWS to increment the counter.

Let me know how it works.

 

[Eric W]

By the way, I should have made clear that the arrays in the function are Named ranges. I made that assumption from seeing your formulas. If that's not the case, the UDF will have to be adjusted.

 

[fees4steez]

Ok so just to be clear I am replacing Array1, Array2, etc with the named ranges in my file? What about myarrays? Thanks for the help again!

 

[Eric W]

The only line you should have to change is the one that starts with

myarrays =

Change the "Array1" etc. to whatever your Names are. I don't mean "Sheet1!G2:G100". I'd imagine something like "JuiceAmts". If you don't know how to create a Name, let me know.

The other line of interest might be the one roughly in the middle with ".Offset(0, -3)" on the end. That's where I get the product name, and based on your formulas, it grabs the contents of the cell 3 columns to the left.

 

[fees4steez]

Ok perfect! Now will this work on other columns? So Array1, 2, 3, 4, and 5 are in the same column on different sheets, and need to be able to add a unique set of 5 arrays for each store, do I just enter them in with columns or do I need to copy that for each store?

 

[fees4steez]

So when I do it, I'm only getting "" as a result. I just replaced Array1, Array2, etc. With the named ranges where they would input their count. I've tried as a standard function and an array function.