SOLVED

Meet criteria in two columns on diff worksheet and populate the worksheet when user enters InvNum

%3CLINGO-SUB%20id%3D%22lingo-sub-1281739%22%20slang%3D%22en-US%22%3EMeet%20criteria%20in%20two%20columns%20on%20diff%20worksheet%20and%20populate%20the%20worksheet%20when%20user%20enters%20InvNum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281739%22%20slang%3D%22en-US%22%3E%3CP%3EAny%20help%20appreciated...Self%20Taught%20Excel%20Enthusiast...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(DataEntry!%24F%242%3A%24F%241000%2C%20IF(AND(DataEntry!%24D%242%3A%24D%241000%3D%24A%241%2CDataEntry!%24C%242%3A%24C%241000%3D%22KomRip%22)%2CROW(DataEntry!%24F%242%3A%24F%241000)-MIN(ROW(DataEntry!%24F%242%3A%24F%241000))%2B1)%2C%20ROWS(%24A%2426%3AA26))%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDataEntry!f2%3Af1000%20%3D%20PO%23%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDataEntry!d2%3Ad1000%3DInvNum%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EA1%20%3D%20user%20entered%20invoice%20number%20on%20shipment%20sheet%20to%20populate%20shipment%20sheet%20%3B%20the%20trigger%20to%20extract%26nbsp%3B%20the%20correct%20records%20from%20the%20DataEntry!%20worksheet%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDataEntry!c2%3Ac1000%20%3D%20CompanyName%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERows(a26%3Aa26)%20%3D%20rows%20on%20the%20shipment%20sheet%20I%20want%20to%20populate%20with%20PO%23%20that%3C%2FP%3E%3CP%3Ematch%20the%20criteria%2C%20InvNum%20and%20CompanyName.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%20I%20get%20a%20Spill%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20using%20the%20following%20formula%2C%20which%20worked%20perfectly%2C%20until%20I%20wanted%20to%20add%20the%20extra%20criteria%20of%20needing%20the%20CompanyName%20AND%20the%20InvNum%20to%20match%20upon%20the%20user%20entry%20of%20the%20InvNum%20used%20to%20populate%20the%20shipment%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(DataEntry!%24F%242%3A%24F%241000%2C%20SMALL(IF(DataEntry!%24D%242%3A%24D%241000%3D%24A%241%2CROW(DataEntry!%24F%242%3A%24F%241000)-MIN(ROW(DataEntry!%24F%242%3A%24F%241000))%2B1)%2C%20ROWS(%24A%2426%3AA27)))%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVery%20convoluted%20for%20a%20newby...Any%20help%20is%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1281739%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281778%22%20slang%3D%22en-US%22%3ERe%3A%20Meet%20criteria%20in%20two%20columns%20on%20diff%20worksheet%20and%20populate%20the%20worksheet%20when%20user%20enters%20InvNu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281778%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F609446%22%20target%3D%22_blank%22%3E%40ocatrina%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20upload%20a%20sample%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281805%22%20slang%3D%22en-US%22%3ERe%3A%20Meet%20criteria%20in%20two%20columns%20on%20diff%20worksheet%20and%20populate%20the%20worksheet%20when%20user%20enters%20InvNu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281805%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F609446%22%20target%3D%22_blank%22%3E%40ocatrina%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281812%22%20slang%3D%22en-US%22%3ERe%3A%20Meet%20criteria%20in%20two%20columns%20on%20diff%20worksheet%20and%20populate%20the%20worksheet%20when%20user%20enters%20InvNu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281812%22%20slang%3D%22en-US%22%3EAs%20I%20am%20looking%20at%20my%20own%20statements%20about%20my%20problem%2C%20would%20I%20need%20another%20cell%2C%20like%20B1%20on%20the%20ShipDetForm%2C%20to%20evaluate%20and%20match%20the%20KomRip%3F%20Can%20A1%20only%20do%20the%20job%20for%20matching%20the%20invoice%20number%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281822%22%20slang%3D%22en-US%22%3ERe%3A%20Meet%20criteria%20in%20two%20columns%20on%20diff%20worksheet%20and%20populate%20the%20worksheet%20when%20user%20enters%20InvNu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281822%22%20slang%3D%22en-US%22%3EThe%20following%20formula%20outputs%20the%20correct%20amount%20of%20PO%23s%20but%20the%20wrong%20ones.%3CBR%20%2F%3E%3CBR%20%2F%3E%7B%3DIFERROR((INDEX(DataEntry!%24F%242%3A%24F%241000%2C%20IF(AND(DataEntry!%24D%242%3A%24D%241000%3D%24A%241%2CDataEntry!%24C%242%3A%24C%241000%3D%24B%241)%2CROW(DataEntry!%24F%242%3A%24F%241000)-MIN(ROW(DataEntry!%24F%242%3A%24F%241000))%2B1)%2C%20ROWS(%24A%2426%3AA26)))%2C%22%22)%22%7D%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281836%22%20slang%3D%22en-US%22%3ERe%3A%20Meet%20criteria%20in%20two%20columns%20on%20diff%20worksheet%20and%20populate%20the%20worksheet%20when%20user%20enters%20InvNu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281836%22%20slang%3D%22en-US%22%3EOK..After%20all%20day%2C%20I%20have%20the%20answer.%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIFERROR(INDEX(DataEntry!%24C%242%3A%24F%241000%2C%20SMALL(IF(COUNTIF(%24A%241%2CDataEntry!%24D%242%3A%24D%241000)*COUNTIF(%24B%241%2CDataEntry!%24C%242%3A%24C%241000)%2CROW(DataEntry!%24C%242%3A%24F%241000)-MIN(ROW(DataEntry!%24C%242%3A%24F%241000))%2B1)%2C%20ROWS(%24A%2426%3AA26))%2CCOLUMN(D1))%2C%22%22)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281837%22%20slang%3D%22en-US%22%3ERe%3A%20Meet%20criteria%20in%20two%20columns%20on%20diff%20worksheet%20and%20populate%20the%20worksheet%20when%20user%20enters%20InvNu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281837%22%20slang%3D%22en-US%22%3ECreated%20%24B%241%20to%20hold%20%22KomRip%22..probably%20wouldn't%20be%20any%20different%20than%20using%20a%20literal%20in%20the%20formula%2C%20but%20more%20useable...just%20in%20case%20I%20want%20to%20use%20it%20for%20other%20companies.%20HA!%3CBR%20%2F%3E%3CBR%20%2F%3EJoined%20the%20columns%20as%20a%20range%20and%20it%20was%20able%20to%20find%20the%20all%20the%20values%20desired%20in%20the%20selected%20column%2C%20D1.%3CBR%20%2F%3E%3CBR%20%2F%3ELIke%20one%20big%20chess%20game...I%20win.....eventually.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281851%22%20slang%3D%22en-US%22%3ERe%3A%20Meet%20criteria%20in%20two%20columns%20on%20diff%20worksheet%20and%20populate%20the%20worksheet%20when%20user%20enters%20InvNu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281851%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F609446%22%20target%3D%22_blank%22%3E%40ocatrina%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DFILTER(DataEntry!F%3AF%2C((DataEntry!C%3AC%3D%24B%241)*(DataEntry!D%3AD%3D%24A%241)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281862%22%20slang%3D%22en-US%22%3ERe%3A%20Meet%20criteria%20in%20two%20columns%20on%20diff%20worksheet%20and%20populate%20the%20worksheet%20when%20user%20enters%20InvNu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281862%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3EThank%20you%20for%20your%20work!%20That%20worked%20on%20A26%20of%20the%20ShipDetForm%2C%20but%20did%20not%20provide%20the%20rest%20of%20the%20POs%20for%20a%20particular%20invoice.%20...%20and%20I%20don't%20know%20enough%20to%20adapt%20it%20to%20do%20that.%20Do%20you%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1282405%22%20slang%3D%22en-US%22%3ERe%3A%20Meet%20criteria%20in%20two%20columns%20on%20diff%20worksheet%20and%20populate%20the%20worksheet%20when%20user%20enters%20InvNu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1282405%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F609446%22%20target%3D%22_blank%22%3E%40ocatrina%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhich%20one%20is%20not%20working%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1282643%22%20slang%3D%22en-US%22%3ERe%3A%20Meet%20criteria%20in%20two%20columns%20on%20diff%20worksheet%20and%20populate%20the%20worksheet%20when%20user%20enters%20InvNu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1282643%22%20slang%3D%22en-US%22%3EYour%20formula%20populates%20the%20first%20PO%20of%20the%20invoice%20over%20and%20over.%20It%20does%20not%20bring%20in%20the%20other%20purchase%20orders%20of%20the%20invoice%20as%20I%20drag%20down%20rows%20A26%3AA44.%20I%20love%20your%20formula%20for%20its%20simplicity%20if%20you%20could%20make%20that%20happen.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1282647%22%20slang%3D%22en-US%22%3ERe%3A%20Meet%20criteria%20in%20two%20columns%20on%20diff%20worksheet%20and%20populate%20the%20worksheet%20when%20user%20enters%20InvNu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1282647%22%20slang%3D%22en-US%22%3EThe%20formula%20I%20got%20to%20work%20would%20populate%20each%20row%2C%20as%20I%20dragged%20the%20formula%20over%20them%2C%20dependent%20upon%20if%20there%20were%20more%20PO%23s%20to%20be%20had%20for%20the%20invoice%2C%20then%20left%20the%20rows%20blank%20when%20there%20were%20no%20more%20to%20be%20moved%20onto%20the%20ShipDetForm.%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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.

11 Replies
Highlighted
Highlighted
Highlighted
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?
Highlighted
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))),"")"}
Highlighted
Best Response confirmed by ocatrina (Occasional Contributor)
Solution
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)),"")
Highlighted
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.
Highlighted

@ocatrina 

=FILTER(DataEntry!F:F,((DataEntry!C:C=$B$1)*(DataEntry!D:D=$A$1)))

Highlighted

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

Highlighted
Highlighted
Your formula populates the first PO of the invoice over and over. It does not bring in the other purchase orders of the invoice as I drag down rows A26:A44. I love your formula for its simplicity if you could make that happen.
Highlighted
The formula I got to work would populate each row, as I dragged the formula over them, dependent upon if there were more PO#s to be had for the invoice, then left the rows blank when there were no more to be moved onto the ShipDetForm.