+ Reply to Thread
Results 1 to 8 of 8

Double data validation list containing duplicate names to display unique names on dropdown

  1. #1
    Registered User
    Join Date
    06-26-2015
    Location
    Brazil
    MS-Off Ver
    O365 Win
    Posts
    63

    Double data validation list containing duplicate names to display unique names on dropdown

    Hi Guys,

    I am trying to make a workbook to make it easier when sending confirmations to my clients, I talk to many people during the day, and I always send an e-mail to both parties of what we talked about. I want to make it automated in order to save time, so here goes:

    I have a sheet called 'List' this is where the message will be, and data will be taken from other sheets, buyer and seller.
    Both Buyer and Seller sheets contains three columns: A is company name, B is Name and C is e-mail address.

    On the List sheet, I have four dropdown validation lists:
    1. Buyer Company name
    2. Buyer from company name
    3. Seller Company name
    4. Seller from company name

    I speak to many people from different companies, and I don't want to show repeated company names - so I made a new sheet where I place the pivot table for both buyer and seller, but I am stuck here, tried reading on OFFSET, but I just couldn't figure it out since I don't have much knowledge in VBA.

    I have attached a sheet to show more or less what I want.

    I appreciate it if anyone can help me :D

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Double data validation list containing duplicate names to display unique names on drop

    You have the right idea to make unique lists by using a pivot table. Now what you need to do is overlay a named dynamic range over them.

    See this wiki for information on the offset command and named dynamic ranges: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges

    I changed your two ranges to:
    t_buyers =OFFSET(Tables!$A$5,0,0,COUNTA(Tables!$A:$A)-3,1)
    t_seller =OFFSET(Tables!$D$5,0,0,COUNTA(Tables!$D:$D)-3,1)
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-26-2015
    Location
    Brazil
    MS-Off Ver
    O365 Win
    Posts
    63

    Re: Double data validation list containing duplicate names to display unique names on drop

    Thanks dflak, it worked!

    I just have two other problems now, the list will be eventually growing with new companies and staff.
    I added a new company for a test, and it did not show on the list.
    1. I need it to organically grow together with the list.
    2. I need a dropbdown list with the contact of the company

    Is it possible to do it?

    Edit: I got the organic growth... I must be tired... I had to refresh the Pivot table, and changed the code to =OFFSET(Pivot!$A$5,0,0,COUNTA(Pivot!$A:$A)-2,1) // -2 instead of -3
    Last edited by danwoltrs; 12-22-2015 at 02:50 PM.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Double data validation list containing duplicate names to display unique names on drop

    Add the buyers and sellers to the Excel tables on the Buyer and Seller tabs. Then refresh the pivot tables on the tables tab. The named ranges should adjust automatically.

    This article explains named ranges and the offset command: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges

  5. #5
    Registered User
    Join Date
    06-26-2015
    Location
    Brazil
    MS-Off Ver
    O365 Win
    Posts
    63

    Re: Double data validation list containing duplicate names to display unique names on drop

    The named ranges were adjusted, thanks, it is now following the formula - I had just forgotten to refresh (eyes are tired)

    Did do some reading there and understood a bit more now on offset and named ranges, nice to understand how it works and how the COUNTA can be used there

    Now on the name of the person I was in touch with, having in mind that one company can have more than one contact (reason why I wanted to slim it down with pivot table), how can I choose the contact?

    For instance, I pick company Wolthers Douqué and the person I was in touch with was Joe, but there are three other choices. Can I do as follows?


    Please Login or Register  to view this content.

    validation with validation and unique names.xlsx

  6. #6
    Registered User
    Join Date
    06-26-2015
    Location
    Brazil
    MS-Off Ver
    O365 Win
    Posts
    63

    Re: Double data validation list containing duplicate names to display unique names on drop

    Tried editing but it would just delete my post above.

    Tried something like this now after doing a bit more research, but still getting errors.

    Please Login or Register  to view this content.

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Double data validation list containing duplicate names to display unique names on drop

    Now you are talking about cascaded dropdown lists. I have a wiki (or 2) for that as well. Take a look them and if you have questions, give a shout:

    There are two ways of doing it:

    Non-VBA: http://www.utteraccess.com/wiki/inde..._%28Non-VBA%29

    and VBA: http://www.utteraccess.com/wiki/inde...ists_%28VBA%29

    I think you are well set up to use the Non-VBA method.

  8. #8
    Registered User
    Join Date
    06-26-2015
    Location
    Brazil
    MS-Off Ver
    O365 Win
    Posts
    63

    Re: Double data validation list containing duplicate names to display unique names on drop

    Thank you very much dflak, it helped me a lot, I got it to work using the NON-VBA method.

    Now I am almost done with my excel, made a new thread, I am stuck on another subject now hehehe, but again, thanks for the help man, I really appreciate it!

+ 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. [SOLVED] Unique names list from data validation column
    By L plates in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-01-2017, 07:08 PM
  2. Replies: 1
    Last Post: 10-06-2014, 09:44 AM
  3. duplicate names with unique data - how to combine?
    By hwishman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-01-2014, 02:39 PM
  4. [SOLVED] Filtering repeated names to get a list of unique names
    By grumpyguppy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-06-2014, 11:48 PM
  5. [SOLVED] All possible unique combination of 16 names from a list that contains 19 names
    By spirit29 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-20-2013, 01:08 PM
  6. List of unique and duplicate names that correspond with dates
    By corbintx in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-09-2012, 01:24 PM
  7. Display Unique Names From a Long List
    By Phillycheese5 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-27-2007, 07:06 PM

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