Forum Discussion

LearningFurther's avatar
LearningFurther
Copper Contributor
Mar 20, 2023

Formula Help

Excel Sheet 2: Column D are the different part numbers we offer. Column E is the quantity ordered on a specific Purchase order. Column H is the quantity shipped from that specific purchase order. ...
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Mar 21, 2023

LearningFurther 

Your formula is close but not exactly correct. You need to use a different approach to find the run out date based on stock and orders.

 

One possible solution is to use a helper column that calculates the cumulative net orders for each part number (Column E minus Column H), and then use a lookup function to find the first date (Column I) that matches or exceeds the stock level (Sheet 1 Column B) for that part number (Sheet 1 Column A).

 

For example, you can add a new column J in Sheet 2 with this formula in J2 and copy it down:

=SUMIFS(E$2:E2,D$2:D2,D2)-SUMIFS(H$2:H2,D$2:D2,D2)

This will give you the cumulative net orders for each part number.

 

Then, in Sheet 1, you can use this formula in C1 and copy it down:

=IFERROR(INDEX(Sheet2!I:I,MATCH(A1&B1,Sheet2!D:D&Sheet2!J:J,1)),“”)

 

This will return the first date that matches or exceeds the stock level for each part number, or a blank if there is no such date.

 

I hope this helps

...is just a suggested solution, if it doesn't fit your plans, please just ignore it :).

 

LearningFurther's avatar
LearningFurther
Copper Contributor
Mar 21, 2023
NikolinoDE I added the first formula into sheet 2,
I then added the formula =IFERROR(INDEX(Sheet2!I:I,MATCH(A1&B1,Sheet2!D:D&Sheet2!J:J,1)),“”)
to sheet 1.
It keeps returning 1/0/1900
Does the lookup function generate automatically?
I tried this but keep getting an error.
=lookup("=IFERROR(INDEX(Sheet2!I:I,MATCH(A1&B1,Sheet2!D:D&Sheet2!J:J,1)),“”)")

How does it read the request dates from Sheet two?

I appreciate your help anything further you can assist with is greatly appreciated!
  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Mar 21, 2023

    LearningFurther 

     

    Did you format the cells as numbers?

     

    This can maybe be, because Excel treats blank cells as zero values when performing calculations, and 1/0/1900 is how Excel displays zero dates.

    One possible solution is to use an IF function to check if the cell is blank before applying the lookup function.

    For example, you can try something like this:

    =IF(ISBLANK(Sheet2!A1),“”,IFERROR(INDEX(Sheet2!I:I,MATCH(A1&B1,Sheet2!D:D&Sheet2!J:J,1)),“”))

     

    This formula will return an empty string if Sheet2!A1 is blank, otherwise it will perform the lookup function as usual.

    • LearningFurther's avatar
      LearningFurther
      Copper Contributor
      Mar 21, 2023

      NikolinoDE  I have attached a sample Excel sheet very simple with just a couple of items
      I still can not get the formula to work, not sure if I am missing something. 

       

      I really do appreciate the help!!

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Mar 21, 2023

        LearningFurther 

        Sub stock()

Dim i, j As Long
Dim sum As Long

For j = 1 To 3
For i = 2 To 100

sum = Application.WorksheetFunction.SumIf(Range(Cells(2, 4), Cells(i, 4)), Sheets("Sheet1").Cells(j, 1).Value, Range(Cells(2, 11), Cells(i, 11)))

If sum > Sheets("Sheet1").Cells(j, 2).Value Then
Sheets("Sheet1").Cells(j, 3).Value = Cells(i, 9).Value
Exit For

Else
End If

Next i
Next j

End Sub

        You can try these lines of code. In the attached file you can click the button in cell B4 of Sheet2 to run the macro.

         

        Which version of Excel do you work with? Do you have Office 365?

Resources