+ Reply to Thread
Results 1 to 4 of 4

Capture a list of data based on values from adjacent row within 1 cell for dropdown list

  1. #1
    Registered User
    Join Date
    08-20-2014
    Location
    Hong Kong
    MS-Off Ver
    2010
    Posts
    5

    Capture a list of data based on values from adjacent row within 1 cell for dropdown list

    Hi all,

    This is my first time posting a thread here. I have encountered a problem here which I found to be quite difficult to combine the formulae with.

    Ultimately, I would like to move the final formula to data validation to produce a "Dependent drop-dwon list". Before I could produce the list, I would need a formula that could match the values from the adjacent row (i.e. below 1 row) to see whihc cell should be included or excluded from the drop-down list.

    I have tried a various combinations of formulae such as index and match, Hlookup, Lookup, offset, IF , but all the formulae that I have used seems to have some bugs that do not give the correct results.

    I have here attached a simple excel file that im working on.

    May I get some assistance or suggestions on how to get a accurate result or that will make this working? Many thanks!!
    Attached Files Attached Files

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Capture a list of data based on values from adjacent row within 1 cell for dropdown li

    Try this...

    Enter this array formula** in A5:

    =IFERROR(INDEX($3:$3,SMALL(IF(D$4:M$4="Y",COLUMN(D$4:M$4)),ROWS(A$5:A5))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down until you get blanks.

    Then, as the source for your drop down list use a formula like this:

    =OFFSET(A5,,,COUNTIF(A5:A20,"?*"))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    08-20-2014
    Location
    Hong Kong
    MS-Off Ver
    2010
    Posts
    5

    Re: Capture a list of data based on values from adjacent row within 1 cell for dropdown li

    Hi Tony,

    That works really well, thanks you very much !

    Geoven

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Capture a list of data based on values from adjacent row within 1 cell for dropdown li

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ 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] Populate in-cell dropdown list with values based on sheet name
    By mks16 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-21-2015, 09:35 AM
  2. Remove Dropdown List if Text in adjacent cell
    By brent_milne in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-17-2014, 05:28 PM
  3. [SOLVED] Display a dropdown list or populate a cell with single value based on other cells' values
    By filla_dilla in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-12-2014, 09:47 AM
  4. Changing other cell values based on Dropdown list
    By FransB in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-25-2014, 04:00 AM
  5. Data Validation List - Dependent on Adjacent Cell - Value from Unsorted List
    By justforthis1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-26-2013, 12:24 PM
  6. Populating numerical values in adjacent cell from using a dropdown list
    By up_and_under in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 06-30-2010, 10:30 AM
  7. Replies: 21
    Last Post: 12-02-2009, 03:27 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