+ Reply to Thread
Results 1 to 13 of 13

Using Data VAlidation to create a list dependant on multiple rows?

Hybrid View

  1. #1
    Registered User
    Join Date
    07-28-2014
    Location
    Aberdeen, Scotland
    MS-Off Ver
    2011 Mac
    Posts
    6

    Using Data VAlidation to create a list dependant on multiple rows?

    I have three individual lists and I am using a formula like this for each of them =OFFSET(Table1,MATCH(F15,Table1,0)-1,1,COUNTIF(Table1,F15),1)

    for my final cell I need to create another data validation list which is depenant on the values selected in the previous three lists.. any idea how I would alter the formula to allow me to do that? I tried using and after the match to match all three tables but it never worked

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Using Data VAlidation to create a list dependant on multiple rows?

    What do you mean by "which is depenant on the values selected in the previous three lists"?

    can you post an excel sheet, showing where you're at and what you want?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Registered User
    Join Date
    07-28-2014
    Location
    Aberdeen, Scotland
    MS-Off Ver
    2011 Mac
    Posts
    6

    Re: Using Data VAlidation to create a list dependant on multiple rows?

    I'll make a basic example tonight with what I mean if needed

  4. #4
    Registered User
    Join Date
    07-28-2014
    Location
    Aberdeen, Scotland
    MS-Off Ver
    2011 Mac
    Posts
    6

    Re: Using Data VAlidation to create a list dependant on multiple rows?

    Like from dropdown list A I choose Client, From dropdown list B I choose Location and dropdown list. In the third dropdown list I need products but the products need to be specific to the cleint and location... thats a simplified example as I am unable to upload the file.

    All the information is in one large table. I made the first two lists using the above formula as that works when it is depdent on one list but not sure how to change it so the formula checks both lists selecting rows where only D15 and F15 match the corresponding values.

  5. #5
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├•┤ Pew Pew
    Posts
    441

    Re: Using Data VAlidation to create a list dependant on multiple rows?

    It will all depend on where you have your list of possibilities and how they are structured.
    Despite the high cost of living, it remains very popular.

    Don't forget to mark threads SOLVED when you get an answer and rep all the geniouses that helped you today!

  6. #6
    Registered User
    Join Date
    07-28-2014
    Location
    Aberdeen, Scotland
    MS-Off Ver
    2011 Mac
    Posts
    6

    Re: Using Data VAlidation to create a list dependant on multiple rows?

    Is there not a basic way to say MATCH Cell B15 with list Table1 and Cell D15 with Table 2 and Cell F15 with Table 3?

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Using Data VAlidation to create a list dependant on multiple rows?

    Possibly, yes. But it is easier to find a solution when we can see what you are talking about!! From the sound of it, assembling a list, dependent on your inputs might be a runner, but I don't want to second guess what you've already got.

  8. #8
    Registered User
    Join Date
    07-28-2014
    Location
    Aberdeen, Scotland
    MS-Off Ver
    2011 Mac
    Posts
    6

    Re: Using Data VAlidation to create a list dependant on multiple rows?

    Example.xlsx

    That is a simplified example of how I have set it out but its on a massive scale and needs to reference all previous columns for the productl ine

  9. #9
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├•┤ Pew Pew
    Posts
    441

    Re: Using Data VAlidation to create a list dependant on multiple rows?

    Not positive this is what you are looking for but put this into E2 and press ctrl + shift + enter

    Formula: copy to clipboard
    =IFERROR(INDEX(Product,MATCH(B2&C2&D2,Client&Country&Location,0)),"")


    If there are more than 1 product per option of the previous 3, and a lot more options of those it is good to show some variety in the sample, along with what you expect the result to be, so we know more of what you are looking for.

  10. #10
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Using Data VAlidation to create a list dependant on multiple rows?

    Hi

    No need array formula from Hawkeye16
    Use this E2
    Formula: copy to clipboard
    =IFERROR(INDEX(Product, MATCH(B2&C2&D2, INDEX(Client&Country&Location, 0), 0)),"")
    enter.

    Regard
    micope21
    To help you by my post? it would be nice to click on to say "Thank you".
    If you are happy with a solution to your problem?
    Click Thread Tools above your first post,
    select "Mark your thread as Solved".

  11. #11
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├•┤ Pew Pew
    Posts
    441

    Re: Using Data VAlidation to create a list dependant on multiple rows?

    Meh. I'd prefer the array formula that is shorter over a non array formula that is longer. Personal preference I suppose. I prefer simpler.

  12. #12
    Registered User
    Join Date
    07-28-2014
    Location
    Aberdeen, Scotland
    MS-Off Ver
    2011 Mac
    Posts
    6

    Re: Using Data VAlidation to create a list dependant on multiple rows?

    Does that formula work in the data validation? Cant seem to get it to work, the cell which the formula is being entered also isnt in line its on another sheet would that cause any issue as its reference the correct cells and lists.

  13. #13
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├•┤ Pew Pew
    Posts
    441

    Re: Using Data VAlidation to create a list dependant on multiple rows?

    There is no need for data validation unless there is more than one value possible, you might as well just use a formula in your example and take the user work out of the equation for that.

    If the example is not showing a lot of the possible combinations or different situations that arise, then we may need to rework our solution when we get a more appropriate example file.

+ 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. Dependant ActiveX Combo Box list based on Data Validation List
    By spalmer28 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-12-2014, 07:57 AM
  2. Data Validation Dependant auto update the list
    By Rocky2013 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-25-2012, 07:38 PM
  3. Multiple Dependant Data Validation
    By exlove in forum Excel General
    Replies: 0
    Last Post: 09-25-2012, 05:22 AM
  4. How to use data validation for Mutiple lists dependant on one list
    By excel66 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-12-2011, 02:21 AM
  5. Dependant Data Validation List
    By Stabilos in forum Excel General
    Replies: 1
    Last Post: 11-21-2005, 01:50 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