Forum Discussion
Solved
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(R...
- 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)),"")
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))),"")"}
{=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))),"")"}
- 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)),"")- Created $B$1 to hold "KomRip"..probably wouldn't be any different than using a literal in the formula, but more useable...just in case I want to use it for other companies. HA!
Joined the columns as a range and it was able to find the all the values desired in the selected column, D1.
LIke one big chess game...I win.....eventually.