Automate drop down list allocation given specific entries

Question

Hi everyone !&nbsp;I'm facing a puzzle for my company trying to automate some entries in drop down lists given some entries in a specific cell.&nbsp;To clarify, I have a file to track my supplier invoices and allocate them to specific P&amp;L levels ( Selling, administration,...) via a drop down list ( See Below)Some suppliers are quite repetitive per month - I would like to automate(given allocation table) the drop down entries for these suppliers but keep the drop down list for potential new ones. Obviously, I was thinking about&nbsp;&nbsp;combining Indirect &amp; Vlookup formula but how to keep drop down list active for new ones ?&nbsp;Any ideas ?&nbsp;Thanks in advance !&nbsp;&nbsp;SUPPLIER Name&nbsp; &nbsp; &nbsp; P&amp;L Account - Level 1(Drop down List)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; P&amp;L Account - Level 2(Drop down)ABCSelling &amp; Market AccessPromotion

mathetes · Answer

Alex1330&nbsp;&nbsp;I created the attached spreadsheet some time ago as a demonstration of what's possible with some of the new Dynamic Array functions. This essentially creates a secondary drop down dynamically based on an initial selection. There's a little bit of documentation in it.&nbsp;You do need the newest release of Excel in order for UNIQUE and FILTER to operate.

alex1330 · Answer

mathetes&nbsp;Thanks for support but I'm not speaking about any dynamic drop down lists here.&nbsp;It's a manual invoice follow-up file for all our suppliers.I would like the drop down lists (in column P&amp;L 1-2) to be automatically fulfilled&nbsp; for our usual suppliers (based on a reference sheet that I compute) &amp; for unusual ones I would like to choose the entry in the drop down list.Would this be possible ?&nbsp;Thanks&nbsp;

mathetes · Answer

Alex1330&nbsp;&nbsp;I think you're going to need to describe more clearly--perhaps by giving some scenarios for usage--what you're seeking. I was giving you an idea, a possibility in response to your having mentioned "obviously" the VLOOKUP function for some aspect of it...so I was suggesting that FILTER could be another way to do that automating....&nbsp;Anyway, what you're describing isn't totally clear. If it's possible to post your actual spreadsheet along with that description it would help. I'm sorry but I'm headed off for a long weekend and won't be responding to posts here until next week, but there are plenty of others who may.&nbsp;&nbsp;

alex1330 · Answer

mathetes&nbsp;I have attached the corresponding file.As an example:&nbsp;1) In "Supplier Inv" sheet, I have received three invoices from three different suppliers. ABC &amp; Fail are usual suppliers for which I usually receive 3/4 invoices per month. It's always the same P&amp;L levels for these two suppliers so I would like column to be automatically computed given allocation in sheet "Usual Supplier Allocation"&nbsp;2) Still in "Supplier Inv" sheet, "XYZ" is a new supplier so I would like the accounting department to manually choose the P&amp;L categorie given drop down list available in column E &amp; F. I do not want the P&amp;L levels to be automatically computed for this supplier as I don't know if I will receive similar invoices in the future.&nbsp;In Brief,&nbsp; in "Supplier Inv" sheet, the logic would be in column&nbsp;E &amp; F&nbsp;-&gt; If Supplier Name is among "usual supplier allocation" list then auto compute given allocation in "usual supplier allocation" list.If not -&gt;Then use drop down lists

mathetes · Answer

Alex1330&nbsp;&nbsp;I've given you a different and, I think, better approach. You were most of the way to this anyway. But instead of having a new supplier get entered and then have your accounting department use the drop down to populate columns E and F, this approach gives you:&nbsp;A drop down for the Supplier Name column (Column A) that is automatically extended any time a new supplier comes along and is added to the business table of suppliers. I moved that to your MasterData tab, by the way, so that all of your business tables are together.&nbsp;So now, under this system:An invoice is received from a supplier,Whoever is entering it goes to the "Supplier Inv" tab, as before, and starts to enter the supplier name.If it's found, all well and good: Columns E and F are automatically populated (using VLOOKUP now)If it's not found, they need to go to the MasterData tab and enter the supplier name and P&amp;L acct level info there, from the drop down list --- i.e., they're doing what you'd want them to do, but just in a different and more permanent place. From here on out that "new supplier" is a known supplier.They then enter that Supplier Name into the SupplierInv tab andColumns E and F are automatically populatedyou're off and running.TRY IT.&nbsp;You'll see you can add a new supplier easily on the MASTERDATA tab, and that new name immediately appears in the drop down list on the Supplier Inv tab.....&nbsp; VLOOKUP takes care of populating columns E and F based on the info that's in Table1, the supplier info table.

Forum Discussion

Automate drop down list allocation given specific entries

5 Replies

Resources