[Solved] Help adding a second reference column

[LordKasen]

So, I just watched this great video explaining how to create a drop-down list with multiple selections. However, I have two columns that I want to reference separate lists of options. Is there a simple way that I can add in another column to be referenced (i.e., in addition to column 13, I also want to create a drop-down, using separate selections in column 20)?

I've attached the code that is currently working great for a single column.

 

[Abrar-ur-Rahman Niloy]

Hello LordKasen,
Excel's data validation creates a dropdown list from a single range at a time. You can manipulate it by combining two lists to create a unique one and then using the list as a source. However, the columns need to be side by side for that.

Here is how you can create a dynamic unique list from two or more columns:
=INDEX(A1:B24,MOD(SEQUENCE(COUNTA(A1:B24))-1,COUNTA(A1:A24))+1,ROUNDUP(SEQUENCE(COUNTA(A1:B24))/COUNTA(A1:A24),0))

I have extended the formula to row 24 to make it dynamic. E and F columns here indicate the row and column argument for the INDEX function. You can also wrap the whole formula in a UNIQUE function if you don't want any repeats from the entries.

Use this list from column C as the Data Validation source; you can have entries from two (or more) different column entries in the dropdown. (For the INDEX function's array argument here, the columns need to by adjacent though)

In case you need multiple selections from the dropdown, you can use the code from the video after that.