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
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
Gold 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?

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

        Would this look over Sheet 1 and Sheet 2?
        Also, I am unable to use this is states it's from an "untrusted" source and will not allow me to open it.
        I have never worked with Macros before, but am willing to learn.
    • NikolinoDE's avatar
      NikolinoDE
      Gold Contributor
      Mar 21, 2023
      I didn't look at it (unfortunately I didn't have enough time - I'll be able to look at it tomorrow when I have time), but change the quotation marks, sometimes they're not comfy when copying.
      • LearningFurther's avatar
        LearningFurther
        Copper Contributor
        Mar 21, 2023
        NikolinoDE I changed the " and I am still returning the same error.
        The date columns are Date
        Everything else is set to general.

        I really do appreciate the help, seems we are very close to figuring this out!

Resources