[Solved] Help adding a second reference column

LordKasen

New member
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.
 

Attachments

  • Excel Capture.JPG
    Excel Capture.JPG
    70 KB · Views: 2
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.
1708855573670.png
 
Last edited:

Online statistics

Members online
0
Guests online
10
Total visitors
10

Forum statistics

Threads
311
Messages
1,378
Members
568
Latest member
WilliamHon
Top