How to automate drop down list allocation given specific entries ?

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 list)
ABC Selling & Market Access Promotion

Have you got an example of any data to show so I can see what you mean to try and help

Are you wanting indepdentant drop down lists, say like two to three?

Hi Guys !

Thanks for your first replies.
I've quickly created the same file but modifying the suppliers.
The idea is the following, in sheet "Supplier Inv" :

1) ABC is a regular supplier (1-2 invoices/month) for which I want column P&L account- Level 1 & 2 to be automatically filled based on sheet "Reference Table".
2) XYZ is a new supplier for which I want our accounting department to manually decide the different P&L levels based on drop down list.

Does this clarify a bit the current need ?
Thanks for your help !

Hello Alex

I have done the automated drop downs how I think you want them and extended quite away down the cells. They are both in the reference table and the supplier invoices. You can automate that sheet even more with drop downs also if you wanted.

Let me know if you want any other help, or if this wasnt what you wanted. If you want me to try and explain how I did it I will, but I will reply next week as I will be away from my computer at weekend.

Please leave me some reputation and feedback.

Hi Katie,

Thanks for your support.
It's not quite it...

The reference table is used to match the usual suppliers ( for which I want an automatic allocation) to their allocated P&L levels.
On sheet "Supplier Inv", I would like that everytime I add a new line with usual suppliers to have their P&L levels automatically filled despite drop down list.
For non-regular suppliers, if I add a new line ( as XYZ supplier) I would like the person in charge to select the P&L levels(in drop down list) depending on the cost nature.

Hello Alex

I think I have misread exactly what you wanted for the reference table. I have put automatic drop down on them too. But reading back you want the details to vlookup into the reference table dont you from the supplier invoice. If this is what you want, send me a message and I will sort it for you

Hi Katie,

I sent you a private message.
So as detailed above, I want an automatic allocation for usual suppliers (given input in reference table) & a drop down list for unusual suppliers - the Lookup value being the supplier name.

Thanks again !

Hey Alex

So from what you have written I have had another look at home. So I have put the drop down lists in the reference sheet. Then created a vlookup within the supplier invoices with if error ontop to remove N/A if there isn't anything in the field, it returns 0 instead of N/A.
The raw data will need to entered into the reference sheet, but any time you enter the supplier into the column a on supplier invoice it will pick up what you require. I copied this down 1000 cells. Let me know if this is what you mean

Do you want a drop down list created for your suppliers in column A

Hi Katie,

Thanks again for your support.

The limit to your solution is that I don't have the drop down list available anymore in supplier invoice & now in the reference sheet , it's completely the opposite of what I wanted...
As said above, the drop down lists should be in "Supplier Invoice" sheet in P&L 1 & 2 columns.

I would like the P&l 1/2 cells to automatically fill the drop down list entries (given reference sheet) & have the choice to choose the entries for unusual suppliers.
Please let do not hesitate to contact me if you would need more info.

Alex,

I can remove from the reference sheet. There isnt an issue with that.

I can put a drop down list in the supplier invoice column for you to select your customers from the reference sheet. The v look up in the supplier invoice (located in both P&L columns then looks at what the customers name is in the reference sheet and assigns the P&L according to what has been entered into .

If nothing is entered it shows as it as 0 in the invoice suppliers list, then you would know that the accounts need to update the reference sheet. The accounts could put the raw entries into their and the supplier sheet could just look up any information given in the reference sheet. It works the same doesnt it?

If you remove all the names from the first column in supplier invoice and enter the names manually that are in the reference sheet you will see the information automatically update.

Katie,

I see what you mean but it wouldn't be adapted to our Business Model.
It isn't that straight forward as sometimes the P&L 1 & 2 will vary from for same supplier that's why I absolutely want a drop down list in Supplier Inv sheet.

Alex,

I didnt know that. So would they not have say Like ABC Account 1, ABC Account 2, then you could assign different categories to them.

Katie,

I have attached the new file.

So in "Supplier Inv" sheet, ABC is a USUAL supplier for which I have filled P&L 1 & 2 levels manually in "Usual supplier" Sheet. I don't any additional formulas in this respective sheet. For these suppliers, I want the drop down list to be automatically filled in supplier Inv sheet.
"FAIL" is a new supplier for which I received an invoice this month & want to manually fill the drop down lists still in "Supplier Inv" sheet.

So I would eventually need through VBA and/or a MAGIC formula allowing to integrate a kind of VLOOKUP formula in drop down list

Hello Alex leave it with me because I have found a VBA code for you. I am trying to build to manually for you to see the logic Sorry if I am going around the houses. I am new to helping others with excel. I am not an expert.

Alex,

I have built a macro to remove the duplicates from the Usual supplier allocation. If you remove the duplicates, you will need to copy the formula from the first cells down to capture information in the supplier invoice sheet.

I can not help any more as I am confused, as you mention the Supplier Inv sheet twice below. Sorry

So in "Supplier Inv" sheet, ABC is a USUAL supplier for which I have filled P&L 1 & 2 levels manually in "Usual supplier" Sheet. I don't any additional formulas in this respective sheet. For these suppliers, I want the drop down list to be automatically filled in supplier Inv sheet.
"FAIL" is a new supplier for which I received an invoice this month & want to manually fill the drop down lists still in "Supplier Inv" sheet.

Alex like I said before If you wanted the supplier invoice sheet to be automated, and filled from the reference sheet, using vlookup idea, the supplier names would need to be like ABC Account 1, ABC Account 2, especially if your P&L can be different for for each. The vlookup looks at the name and cell references. I could do this and add drop down list also, but unable if I have the same name but with different P&L. the sheet wouldnt know which ABC to look at.

Sorry if I have not solved this. I have given you several ideas.

I'm afraid you're not getting it right as I mentioned "Fail" is an usual supplier and you have added it in "usual supplier allocation" list which is not what I mentioned.

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

Alex,

I have made a quick excel sheet again for you to look over.
So ABC are in your usual supplier list. If you select these it will automatically drop down their normal P&L Account however, with the drop down function you can change the categories and it will not affect anything.
Fail you said above is an usual, but I think you meant unusual. This can just be selected. If this is correct, then I can tidy the N/A etc up for you. Let me know.

This is what I did previously but my version was a little bit more advanced.

Do not delete the formula or N/A just select the drop down if you need to change the categories.

Hi Katie,

Thanks for your wonderful support ! I appreciate it a lot.
That's exactly what I was looking for.

Haven't even thought of integrating directly the VlookUp formula in the cell & combining it with DropDown list...

Big thanks again !

Alex if this is what you can, then send me the final draft over and I will tidy it up for you. Look forward to completing this for you.

I will integrate your proposal to our final file.

One last thing maybe- Do you have any idea how we could freeze the entries (meaning convert formulas to text) in Supplier Inv sheet once the allocation has been made. I'm saying this imagining I would eventually change the allocation for usual supplier in "Usual supplier allocation sheet". I would like to keep the history & with VlookUp formula staying in column E & F it would then be modified.

Does this clarify ?

Alex do you mean all the N/A, I can covert the forumula to 0 or blank, then this wouldnt show. This is what I did previously.

I would have to use iferror(vlookup) function. Send me over the final sheet and then I can have a look for you. I have been creating my own database recently. I can assist with this anytime.

I think what you mean is once the entry is complete then it cant be altered? Do you mean lock the formula in so no one can edit this?

That's exactly it - I want the entry not to be altered but fixed in order to keep the history

i will certainly try and find out for you. I have wanted to same thing on my work to. Dont forget to send the final draft over and I will set it up for you and adjust the ranges to over a 1000 rows? would this be enough?

Hi Katie !

What do you mean by final draft ? I think the last file you shared was already the solution I was looking for.
Just need to finalize it by "freezing" the historical entries

I will implement it in original file - Don't hesitate if you would need any advise for your work as well !

Alex, I mean the final piece that you want me to amend, I will update the vlookup properley and make sure the list range covers the entire column for 1000's of rows. I just did it quickly.

If that was the one which is final I will amend on my copy and update it with the freezing when I have worked it out for you.

Alex, please watch this video as this is what you would like to happen

PHP Code: 

https://youtu.be/yH3zPULELAM 


. any questions or need help just message me. I got this to work in my sheets.

Hi Katie,

Yes not a bad idea !
Sorry I forgot to revert back. Still haven't gone live with new solution.
So at this stage, the only thing I'm missing is just to make sure that history isn't modified due to VlookUp function.

For example, in file attached,on sheet " supplier inv" in cell E6 I want to make sure that the entry is looked and won't be modifed.
Would you have any idea how to implement this ?

Thanks again !

You could lock each line, password protect? By highlighting, using format cells? Or maybe the VBA on the video.

The history will not be modified from the vlookup Alex as it is reading it from the Usual Supplier Invoice List. The only way that it will change is if you change the history on the Usual Supplier list or overwrite with the drop down.

If you make any changes this is what the drop down list is for too be able to over write, like you said that some suppliers have different categories at times.