If function with one result being a Dropdown choice based on criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2282563%22%20slang%3D%22en-US%22%3EIf%20function%20with%20one%20result%20being%20a%20Dropdown%20choice%20based%20on%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282563%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20am%20looking%20to%20write%20an%20if%20function%2C%20where%20if%20the%20the%20first%20criteria%20is%20met%20(invoice%20number%20is%20found%20in%20a%20different%20table)%2C%20then%20a%20dropdown%20box%20of%20results%20that%20match%20that%20invoice%20number%20in%20the%20second%20table%20is%20available.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3ETbl_Data%20column%20b%26amp%3BC%20is%20data%20report%20driven%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ETbl_Data%20column%20d%20is%20where%20I%20am%20developing%20the%20formula%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3ETbl%20Names%20is%20Manually%20created%20after%20investigating%20invoices.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESome%20invoices%20have%20only%20one%20name%2C%20some%20invoices%20have%20multiple%20names.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EThe%20data%20only%20ever%20shows%20the%20first%20name%20on%20the%20invoice.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ETbl_Names%20is%20a%20manual%20breakout%20of%20invoices%20with%20Manual%20names.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThe%20goal%20is%20to%20create%20a%20formula%2C%20that%20if%20the%20invoice%20number%20in%20the%20Tbl_Data%20shows%20on%20Tbl_Names%2C%20a%20data%20validation%20dropdown%20shows%20up%20in%20column%20D%20for%20only%20the%20names%20related%20to%20that%20particular%20invoice%20Number%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EIf%20the%20invoice%20is%20not%20in%20Tbl_names%2C%20it%20should%20show%20the%20name%20in%20Column%20B.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Tbl_names%20would%20have%20at%20most%2015%20names%20per%20invoice%20and%20a%20max%20total%20%23of%20names%20of%20150.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2282563%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-2283083%22%20slang%3D%22en-US%22%3ERe%3A%20If%20function%20with%20one%20result%20being%20a%20Dropdown%20choice%20based%20on%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2283083%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F486340%22%20target%3D%22_blank%22%3E%40MrStern%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20is%20to%20create%20somewhere%20to%20the%20right%20of%20the%20table%20helper%20range%2Ftable%20with%20formulas%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(TRANSPOSE(FILTER(Table2%5BNames%5D%2CTable2%5B%5BInvoice%20%5D%5D%3D%20Tbl_Data5%5B%40Invoice%5D))%2CTbl_Data5%5B%40%5BData%20name%5D%5D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20do%20data%20validation%20on%20it%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I am looking to write an if function, where if the the first criteria is met (invoice number is found in a different table), then a dropdown box of results that match that invoice number in the second table is available.

Tbl_Data column b&C is data report driven
Tbl_Data column d is where I am developing the formula

Tbl Names is Manually created after investigating invoices.
Some invoices have only one name, some invoices have multiple names.
The data only ever shows the first name on the invoice.
Tbl_Names is a manual breakout of invoices with Manual names.

The goal is to create a formula, that if the invoice number in the Tbl_Data shows on Tbl_Names, a data validation dropdown shows up in column D for only the names related to that particular invoice Number
If the invoice is not in Tbl_names, it should show the name in Column B.

 

The Tbl_names would have at most 15 names per invoice and a max total #of names of 150.

1 Reply

@MrStern 

As variant is to create somewhere to the right of the table helper range/table with formulas

=IFERROR(TRANSPOSE(FILTER(Table2[Names],Table2[[Invoice ]]= Tbl_Data5[@Invoice])),Tbl_Data5[@[Data name]])

and do data validation on it