Forum Discussion
Automate drop down list allocation given specific entries
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
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.
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 listsI'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
- Whoever is entering it goes to the "Supplier Inv" tab, as before, and starts to enter the supplier name.
- 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.
- An invoice is received from a supplier,