+ Reply to Thread
Results 1 to 5 of 5

Auto populating cell values from list based on criteria (Source: Separate excel file)

  1. #1
    Registered User
    Join Date
    03-22-2013
    Location
    Roskilde, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    5

    Auto populating cell values from list based on criteria (Source: Separate excel file)

    I am trying to find a way to auto populate cells (either entire row or individial columns) based on criteria value in a dropdown list.
    The source data is located in a separate excel file. This excel file is generated from a database and will update monthly. The column headers will remain the same always.

    The criteria lists will be made in the "City", "Product", "Indication" fields in the Customer tool file. So basically, if I choose "City" the data from the Database.xls file corresponding to city should be displayed in the area below in the Customer tool.

    Both files can be downloaded from here:

    http://basbous.dk/test/customertool.xls
    http://basbous.dk/test/database.xls


    I am thinking autofilter with some kind of macro to take care of the dynamic source list?
    I am not sure how to go about this

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Auto populating cell values from list based on criteria (Source: Separate excel file)

    Hi John,

    welcome to the forum.

    Try using below formula:-
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Enter this using key combination: ctrl shift enter

    See attached:-database.xls customertool.xls

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Registered User
    Join Date
    03-22-2013
    Location
    Roskilde, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Auto populating cell values from list based on criteria (Source: Separate excel file)

    Hi,

    Thank you for that awesome formular. I am dissecting it at the moment trying to figure out what it does exactly

    Let's say I want to add another criteria: Product and Indication. So if I write eg "Product1" it will only show Product1 names for people living in that city (first criteria). Is the difficult to accomplish?

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Auto populating cell values from list based on criteria (Source: Separate excel file)

    You just follow the references from the workbook and apply that with another criteria (i.e, product here )


    Regards,
    DILIPandey
    <click on below * if this helps>

  5. #5
    Registered User
    Join Date
    03-22-2013
    Location
    Roskilde, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Auto populating cell values from list based on criteria (Source: Separate excel file)

    I think I understand that concept - or maybe not quite. If I change the lookup column to match a new criteria (say "Indication") - won't it just overwrite the previous criteria? That's what happened to me. Shouldn't there be some kind of condition check "If criteria equals "Product1" && criteria equals Indication4" only display those results.

    I've attached the file again. Here I've made the first criteria as Product which shows all names, indications and cities connected to that product. What I want to do is, is then select an indication and city which will further narrow/filter the search. So if product criteria equals "Product1" and indication criteria equals "Indication4" , the only name to appear should be John Basbous as this would be the only match. This is where I thought some sort of condition check should be implemented.

    Does this make any sense?

    customertool.xlsdatabase.xls

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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