Forum Discussion
Meet criteria in two columns on diff worksheet and populate the worksheet when user enters InvNum
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.
- OK..After all day, I have the answer.
=IFERROR(INDEX(DataEntry!$C$2:$F$1000, SMALL(IF(COUNTIF($A$1,DataEntry!$D$2:$D$1000)*COUNTIF($B$1,DataEntry!$C$2:$C$1000),ROW(DataEntry!$C$2:$F$1000)-MIN(ROW(DataEntry!$C$2:$F$1000))+1), ROWS($A$26:A26)),COLUMN(D1)),"")
11 Replies
=FILTER(DataEntry!F:F,((DataEntry!C:C=$B$1)*(DataEntry!D:D=$A$1)))
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?
- As I am looking at my own statements about my problem, would I need another cell, like B1 on the ShipDetForm, to evaluate and match the KomRip? Can A1 only do the job for matching the invoice number?
- The following formula outputs the correct amount of PO#s but the wrong ones.
{=IFERROR((INDEX(DataEntry!$F$2:$F$1000, IF(AND(DataEntry!$D$2:$D$1000=$A$1,DataEntry!$C$2:$C$1000=$B$1),ROW(DataEntry!$F$2:$F$1000)-MIN(ROW(DataEntry!$F$2:$F$1000))+1), ROWS($A$26:A26))),"")"}
Please upload a sample workbook.
