+ Reply to Thread
Results 1 to 38 of 38

How to automate drop down list allocation given specific entries ?

  1. #1
    Registered User
    Join Date
    08-14-2020
    Location
    Brussels, Belgium
    MS-Off Ver
    10
    Posts
    13

    Exclamation 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

  2. #2
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

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

  3. #3
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

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

  4. #4
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    message removed.
    Last edited by KatieA; 08-14-2020 at 11:01 AM.

  5. #5
    Registered User
    Join Date
    08-14-2020
    Location
    Brussels, Belgium
    MS-Off Ver
    10
    Posts
    13

    Re: How to automate drop down list allocation given specific entries ?

    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 !
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Cool Re: How to automate drop down list allocation given specific entries ?

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

  7. #7
    Registered User
    Join Date
    08-14-2020
    Location
    Brussels, Belgium
    MS-Off Ver
    10
    Posts
    13

    Re: How to automate drop down list allocation given specific entries ?

    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.

  8. #8
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    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

  9. #9
    Registered User
    Join Date
    08-14-2020
    Location
    Brussels, Belgium
    MS-Off Ver
    10
    Posts
    13

    Re: How to automate drop down list allocation given specific entries ?

    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 !

  10. #10
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Thumbs up Re: How to automate drop down list allocation given specific entries ?

    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
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

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

  12. #12
    Registered User
    Join Date
    08-14-2020
    Location
    Brussels, Belgium
    MS-Off Ver
    10
    Posts
    13

    Re: How to automate drop down list allocation given specific entries ?

    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.

  13. #13
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    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.

  14. #14
    Registered User
    Join Date
    08-14-2020
    Location
    Brussels, Belgium
    MS-Off Ver
    10
    Posts
    13

    Re: How to automate drop down list allocation given specific entries ?

    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.

  15. #15
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    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.

  16. #16
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    I could have another look if you want, but not sure on the formulas needed for the reference sheet, as the supplier names will be classed as duplicates. I will have a look later.

    So invoice suppliers - need all drop downs.
    Information to transfer into the reference sheet from the invoice supplier columns (name, P&1 & P&L 2.)
    Last edited by KatieA; 08-19-2020 at 04:31 AM.

  17. #17
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    Alex i have another idea if you leave it with me mate
    Last edited by KatieA; 08-19-2020 at 05:01 AM.

  18. #18
    Registered User
    Join Date
    08-14-2020
    Location
    Brussels, Belgium
    MS-Off Ver
    10
    Posts
    13

    Re: How to automate drop down list allocation given specific entries ?

    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

  19. #19
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    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.

  20. #20
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    no message
    Last edited by KatieA; 08-19-2020 at 06:24 AM.

  21. #21
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    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.

  22. #22
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    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.

  23. #23
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    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.

  24. #24
    Registered User
    Join Date
    08-14-2020
    Location
    Brussels, Belgium
    MS-Off Ver
    10
    Posts
    13

    Re: How to automate drop down list allocation given specific entries ?

    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

  25. #25
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    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.

  26. #26
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

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

  27. #27
    Registered User
    Join Date
    08-14-2020
    Location
    Brussels, Belgium
    MS-Off Ver
    10
    Posts
    13

    Re: How to automate drop down list allocation given specific entries ?

    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 !

  28. #28
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    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.

  29. #29
    Registered User
    Join Date
    08-14-2020
    Location
    Brussels, Belgium
    MS-Off Ver
    10
    Posts
    13

    Re: How to automate drop down list allocation given specific entries ?

    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 ?

  30. #30
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    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.

  31. #31
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    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?

  32. #32
    Registered User
    Join Date
    08-14-2020
    Location
    Brussels, Belgium
    MS-Off Ver
    10
    Posts
    13

    Re: How to automate drop down list allocation given specific entries ?

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

  33. #33
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    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?

  34. #34
    Registered User
    Join Date
    08-14-2020
    Location
    Brussels, Belgium
    MS-Off Ver
    10
    Posts
    13

    Re: How to automate drop down list allocation given specific entries ?

    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 !

  35. #35
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    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.

  36. #36
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    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.

  37. #37
    Registered User
    Join Date
    08-14-2020
    Location
    Brussels, Belgium
    MS-Off Ver
    10
    Posts
    13

    Re: How to automate drop down list allocation given specific entries ?

    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 !

  38. #38
    Registered User
    Join Date
    05-27-2020
    Location
    Bradford
    MS-Off Ver
    Excel 365
    Posts
    97

    Re: How to automate drop down list allocation given specific entries ?

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Automate lead allocation in excel
    By ra1 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-14-2019, 10:42 PM
  2. Replies: 6
    Last Post: 02-16-2017, 05:08 PM
  3. Replies: 5
    Last Post: 12-08-2015, 01:47 PM
  4. [SOLVED] Place text in a specific cell based on two separate drop down list entries
    By fazthfc in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-24-2015, 03:34 PM
  5. 'same entries':Drop Down List
    By warburger in forum Excel General
    Replies: 2
    Last Post: 08-05-2006, 02:35 PM
  6. How many entries can be put into a drop down list in excel?
    By Mary A in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-26-2006, 04:10 PM
  7. [SOLVED] I would like to know how to automate graph using drop down list
    By Lokesh in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-17-2005, 12:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1