+ Reply to Thread
Results 1 to 6 of 6

Help: create dropdown list based on cell that contains cell which is linked to checkbox

  1. #1
    Registered User
    Join Date
    02-24-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    3

    Help: create dropdown list based on cell that contains cell which is linked to checkbox

    Hello everyone,

    Hope you can help me with the following issue that I can't seem to make work on my own.

    I have a list with checkboxes for users to make a selection of the metrics they want to assess/report in the 'metrics selections' tab. Based on the users' selection, all the cell values that showed up as 'TRUE' will be listed underneath eachother in the 'overview' tab. I want to connect a list dropdown based on the value that is found and listed in the first column in the column behind it. The list values are shown in the 'lists' tab. So this goes against the principle of the cascading dropdown menu's. I want to simply generate a dropdown list based on the metric value without that cell being the main dropdown menu.

    Please see the attached file so it should make sense. If you have any questions, please let me know.

    Many thanks in advance!

    Cheers,

    Remon
    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
    5,838

    Re: Help: create dropdown list based on cell that contains cell which is linked to checkbo

    Please try at
    A2:A8
    =IFERROR(INDEX('Metric Selections'!A:A,AGGREGATE(15,6,ROW('Metric Selections'!$D$2:$D$8)/('Metric Selections'!$D$2:$D$8>""),ROWS($A$2:A2))),"")


    Data validation list at B2:B6

    =OFFSET(Lists!$A$2,,RIGHT($A2)-1,COUNTA(INDEX(Lists!$A$2:$G$9,,RIGHT($A2))))
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-24-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    3

    Re: Help: create dropdown list based on cell that contains cell which is linked to checkbo

    Hi Bo_Ry,

    Thanks a million for your help thus far! I really appreciate it.

    I've tried to re-create this, but the list validation tells me 'the source currently evaluates to an error. do you want to continue?'

    What am I missing? I've attached the sheet which I built based off the information you've provided. Maybe I didn't entirely get it right.

    - I made the selection boxes on the same tab as the dropdown lists, maybe here's where it goes wrong?
    - I also had a merging of cells done as i want to include 3x the same dropdown per selection. (also tried it without merged cells first, but no success).

    I marked the yellow Cell with the list validation that is not functioning as intended (i also want to apply it to the soft yellow marked cells).
    Attached Files Attached Files

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,838

    Re: Help: create dropdown list based on cell that contains cell which is linked to checkbo

    Please try
    =OFFSET(Lists!$A$2,,MATCH(A16,Lists!$A$1:$M$1,)-1,COUNTA(INDEX(Lists!$A$2:$G$9,,MATCH(A16,Lists!$A$1:$M$1,)-1)))
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-24-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    3

    Re: Help: create dropdown list based on cell that contains cell which is linked to checkbo

    Hi Bo_Ry,

    Thanks once again. Another step closer to achieving my goals! However, now it appears that the lists from the dropdowns are limited to only show a number of fields in the dropdown based on the list in the column before the one where it matches the header column. This means, I have to re-arrange the columns from large to small (left to right) in order to make sure all the data will be shown correctly. However, this runs me into the problem that If I edit the lists tab and add/remove data, it would have to be re-arranged every time this happens.

    I've made an example again for this to demonstrate the issue at hand. Can this be worked around somehow? or will re-arranging the columns from large to small be the only way?
    Attached Files Attached Files

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,838

    Re: Help: create dropdown list based on cell that contains cell which is linked to checkbo

    Sorry, Please delete -1 in red
    =OFFSET(Lists!$A$2,,MATCH(A16,Lists!$A$1:$M$1,)-1,COUNTA(INDEX(Lists!$A$2:$G$9,,MATCH(A16,Lists!$A$1:$M$1,)-1)))

    =OFFSET(Lists!$A$2,,MATCH(A16,Lists!$A$1:$M$1,)-1,COUNTA(INDEX(Lists!$A$2:$M$40,,MATCH(A16,Lists!$A$1:$M$1,))))

+ 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. Create Hyperlink based on external linked cell
    By Jacop in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-27-2019, 08:26 AM
  2. Replies: 3
    Last Post: 07-19-2018, 09:32 AM
  3. [SOLVED] Create a list with dropdown linked to a reference no.
    By Roma1r in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-04-2018, 11:39 AM
  4. How to create a dropdown checkbox list on each row
    By Limitless76 in forum Excel General
    Replies: 1
    Last Post: 02-12-2014, 06:39 PM
  5. Lock cell linked to checkbox when checkbox is ticked
    By simeony003 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2013, 10:08 AM
  6. macro to unhide one cell based on the dropdown list value from the cell above
    By famu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-28-2013, 02:00 PM
  7. Macro cannot hide rows based on cell linked to checkbox
    By fernaldd in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-04-2009, 11:46 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