+ Reply to Thread
Results 1 to 9 of 9

displaying list based on data selection in drop down of data validation

  1. #1
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

    displaying list based on data selection in drop down of data validation

    hi,

    Pls find enclosed in attachment a sample file.I have a list of data from A2:C18.The existing list is a sample only.In reality it is more than 2000.Here an user input data in E3.Now based on input data in E3, a selective list only from B column must be shown in F3 (and not all) by matching the data of E3 from A column.
    Similary,in G3 column, selective list only must be shown based on selection of E3 and F3 so that an user can select.

    the same would be for E4,E5 and so on.

    if anyone require further info then kindly let me know.

    Hope you did understand.

    With Rgds,
    Suresh
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: displaying list based on data selection in drop down of data validation

    If I understand you correctly, try this ARRAY formula in F4, copied down as far as you need, and then copied across...
    =IFERROR(INDEX(B$3:B$18,SMALL(IF($A$3:$A$18=$E$3,ROW($A$3:$A$18)),ROWS($A$1:A1))),"")
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

    Re: displaying list based on data selection in drop down of data validation

    Thanx for the prompt reply.

    No sir,I did not want that formula.

    I want in dropdownlist here in E3=2784.Now in F3 I want in dropdown unique value from B column that matches E3 = A column data.I want in a drop down in a sense that sometimes what happens A column data here for instance given 2784 has repeated 4 times in A column and B column has 4 times repeated apple, in reality it might be such that 3 times repeated apple and next 1 might be other .So,here I want to display unique values only.

    Similarly the G column data based on selection of E and F column data.

    Hope now it has been understood.If further information,kindly let me know.

    With Rgds,
    Suresh

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: displaying list based on data selection in drop down of data validation

    provide a sample please

  5. #5
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

    Re: displaying list based on data selection in drop down of data validation

    Dear Sir,

    Pls find enclosed in attachment.I have done for two data taking some specific data list only of corresponding matches and not the entire as I do not know how to do.All this I want to be taken in taking entire range of data range.

    With Best Rgds,
    Suresh
    Attached Files Attached Files

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: displaying list based on data selection in drop down of data validation

    Quote Originally Posted by paradise2sr View Post
    Dear Sir,

    Pls find enclosed in attachment.I have done for two data taking some specific data list only of corresponding matches and not the entire as I do not know how to do.All this I want to be taken in taking entire range of data range.

    With Best Rgds,
    Suresh
    I don't know if this does what you want. Let us know.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

    Re: displaying list based on data selection in drop down of data validation

    Dear Sir,

    Thanks for the reply.
    In F3,you have linked the formula with G3.How that is possible ? Becoz G3 is dependent on F3.

    Let me explain entire scenario again.After that,kindly try to achieve the desired result.

    When I select 2784 which is in E3.Now in F3 it should display list in dropdown---- Apple
    in G3 it should display list in dropdown

    Xzone Salt
    Y Agarbatti
    Zone Sail
    XA
    X2

    When I select 3245 which is in E4.

    Now in F3 it should display list in dropdown
    Orange
    Pomegrante

    in G3 it should display list in dropdown (If in F3 Orange is selected)
    Everest Agrabatti
    Wheel lemon Surf

    or,

    in G3 it should display list in dropdown (If in F3 Pomegrante is selected)

    Shake well Soap

    Hope now this has been transparent now.This is only a sample data.

    With Best Rgds,

    Suresh
    Last edited by paradise2sr; 12-15-2014 at 06:01 AM.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: displaying list based on data selection in drop down of data validation

    Not sure I have the concept yet, but please try this one.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

    Re: displaying list based on data selection in drop down of data validation

    Thanks for the reply.

    Sadly to state,its also not working.This is so becoz. as enclosed in your attachment #post 8,F3 & G3 you have done taken only partial data in helper column J & K rather it should have taken entire column B and C.

    In addition to it, when 'Orange' is selected in F3 from dropdownlist then in G3,it must display only two items and not three becoz correponding 'Orange' & E3 has only two items in C column which is also not displayed in the list.It is displaying entire list of E3 (3245).

    Hope now you got it.

    Someone has stated the below link.But I could not get into it.Perhpas

    http://chandoo.org/wp/2014/02/25/rob...s-without-vba/

    With Best Rgds,
    Suresh

+ 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. Updating cells based on selection from drop down list data validation
    By excelstun in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-18-2016, 05:11 PM
  2. [SOLVED] Updating cells based on selection from drop down list data validation
    By jingles9 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-24-2013, 02:57 PM
  3. Replies: 5
    Last Post: 03-14-2013, 02:38 PM
  4. Data validation drop down list with several selection
    By toblju in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-18-2012, 12:48 AM
  5. Replies: 8
    Last Post: 07-02-2009, 07:13 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