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

[RemonP]

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

[Bo_Ry]

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))))

[RemonP]

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).

[Bo_Ry]

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)))

[RemonP]

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?

[Bo_Ry]

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,))))