Apr 04 2020 02:53 PM
Any help appreciated...Self Taught Excel Enthusiast...
=IFERROR(INDEX(DataEntry!$F$2:$F$1000, IF(AND(DataEntry!$D$2:$D$1000=$A$1,DataEntry!$C$2:$C$1000="KomRip"),ROW(DataEntry!$F$2:$F$1000)-MIN(ROW(DataEntry!$F$2:$F$1000))+1), ROWS($A$26:A26)),"")
DataEntry!f2:f1000 = PO#
DataEntry!d2:d1000=InvNum
A1 = user entered invoice number on shipment sheet to populate shipment sheet ; the trigger to extract the correct records from the DataEntry! worksheet
DataEntry!c2:c1000 = CompanyName
Rows(a26:a26) = rows on the shipment sheet I want to populate with PO# that
match the criteria, InvNum and CompanyName.
Currently I get a Spill error.
I was using the following formula, which worked perfectly, until I wanted to add the extra criteria of needing the CompanyName AND the InvNum to match upon the user entry of the InvNum used to populate the shipment sheet.
=IFERROR(INDEX(DataEntry!$F$2:$F$1000, SMALL(IF(DataEntry!$D$2:$D$1000=$A$1,ROW(DataEntry!$F$2:$F$1000)-MIN(ROW(DataEntry!$F$2:$F$1000))+1), ROWS($A$26:A27))),"")
Very convoluted for a newby...Any help is greatly appreciated.
Apr 04 2020 03:39 PM
Please upload a sample workbook.
Apr 04 2020 04:07 PM
Apr 04 2020 04:19 PM
Apr 04 2020 04:37 PM
Apr 04 2020 05:04 PM
SolutionApr 04 2020 05:15 PM
Apr 04 2020 05:44 PM
=FILTER(DataEntry!F:F,((DataEntry!C:C=$B$1)*(DataEntry!D:D=$A$1)))
Apr 04 2020 06:10 PM
@Patrick2788Thank you for your work! That worked on A26 of the ShipDetForm, but did not provide the rest of the POs for a particular invoice. ... and I don't know enough to adapt it to do that. Do you?
Apr 05 2020 06:32 AM
Which one is not working?
Apr 05 2020 09:12 AM
Apr 05 2020 09:16 AM
Apr 04 2020 05:04 PM
Solution