+ Reply to Thread
Results 1 to 4 of 4

Cascading data validation with mutllti word values

  1. #1
    Registered User
    Join Date
    06-22-2011
    Location
    Kansas City, MO
    MS-Off Ver
    Excel 2007
    Posts
    18

    Cascading data validation with mutllti word values

    Hope i selected the right thread.
    Being charged to create a mutli-select data validation list where user selects a Goal from a drop down. once selected, the next drop down would contain ideas on how to achieve that goal.

    the selection of data is basically sentences, multi-word.. I know how get the first drop down.. I'm struggling to get the second on to work.
    I've attached a brief example. of course there is a big list of each one.

    Column A contains the first drop down values (see cell a12) i need B12 to reflect the list of items in C3 thru F5 to appear based on value.


    The process can't be too complicated.
    I tried =index with match.. but get #N/A guessing because of all the spaces. I'm good with creating some sort of table for each. but not sure how to get Metric to take list and substitute with a different value..

    Ie A3 means SM04a then take data in C2 thru c5 and call it SM04a

    Hope this makes sense.

    Thanks In advance
    Steve
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Cascading data validation with mutllti word values

    Please try Data validation list below

    if you don't mind empty list
    B12
    =INDEX($C$3:$F$5,,MATCH(A12,$C$2:$F$2,))


    or without empty list
    B13
    =OFFSET($B$3,,MATCH(A12,$C$2:$F$2,),COUNTA(INDEX($C$3:$F$6,,MATCH(A12,$C$2:$F$2,))))
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-22-2011
    Location
    Kansas City, MO
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Cascading data validation with mutllti word values

    Thank you for this. Both definitely worked on my example. HOWEVER, I didn't think about the real work sheet. This doesn't seem to be working on the sheet when the data is on a separate tab.
    Even on the attached work sheet, I created another scenario. I copied the various data to Sheet2 and tried to do the data validation on sheet 3.
    the first reference work after i MANUALLY specified sheet2, when i tried to create the Match option and specify sheet2,
    I got the error stating that i couldn't use reference to other work books or worksheets.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Cascading data validation with mutllti word values

    If you could provide a sample that demonstrates the issue (shows examples of what you want), perhaps someone will be able to suggest a work around.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Replies: 12
    Last Post: 11-07-2017, 10:30 AM
  2. Replies: 5
    Last Post: 10-11-2017, 07:27 PM
  3. [SOLVED] Cascading Data Validation Help Needed
    By snapfade in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-12-2017, 07:27 PM
  4. ActiveX Combobox VBA not working on Cascading Data Validation
    By ringonohitorigoto in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-03-2016, 03:33 PM
  5. Replies: 3
    Last Post: 07-28-2014, 03:16 PM
  6. Replies: 5
    Last Post: 07-28-2014, 11:53 AM
  7. Cascading Data Validation -eliminate duplicates
    By coachcr in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-15-2008, 11:39 AM

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