Automate drop down list allocation given specific entries

[Alex1330]

Hi everyone ! 

I'm facing a puzzle for my company trying to automate some entries in drop down lists given some entries in a specific cell. 
To clarify, I have a file to track my supplier invoices and allocate them to specific P&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  combining Indirect & Vlookup formula but how to keep drop down list active for new ones ? 

Any ideas ?

Thanks in advance ! 

SUPPLIER Name      P&L Account - Level 1(Drop down List)            P&L Account - Level 2(Drop down)

ABC

Selling & Market Access

Promotion

[#2]

@Alex1330 

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.

You do need the newest release of Excel in order for UNIQUE and FILTER to operate.

[#3]

@mathetes Thanks for support but I'm not speaking about any dynamic drop down lists here.

It's a manual invoice follow-up file for all our suppliers.
I would like the drop down lists (in column P&L 1-2) to be automatically fulfilled  for our usual suppliers (based on a reference sheet that I compute) & for unusual ones I would like to choose the entry in the drop down list.

Would this be possible ?

Thanks 

[#4]

@Alex1330 

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....

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.

[#5]

@mathetes 

I have attached the corresponding file.

As an example: 

1) In "Supplier Inv" sheet, I have received three invoices from three different suppliers. ABC & Fail are usual suppliers for which I usually receive 3/4 invoices per month. It's always the same P&L levels for these two suppliers so I would like column to be automatically computed given allocation in sheet "Usual Supplier Allocation" 

2) Still in "Supplier Inv" sheet, "XYZ" is a new supplier so I would like the accounting department to manually choose the P&L categorie given drop down list available in column E & F. I do not want the P&L levels to be automatically computed for this supplier as I don't know if I will receive similar invoices in the future. 

In Brief,  in "Supplier Inv" sheet, the logic would be in column E & F -> If Supplier Name is among "usual supplier allocation" list then auto compute given allocation in "usual supplier allocation" list.If not ->Then use drop down lists

[#6]

@Alex1330 

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:

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.

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&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 and
    • Columns E and F are automatically populated
• you're off and running.

TRY IT.

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.....  VLOOKUP takes care of populating columns E and F based on the info that's in Table1, the supplier info table.