Solved How do make a validation list with multiple columns

August 29, 2016 at 08:14:35
Specs: Windows 7
I have data that I need included in the drop down list in multiple columns. If I try to add all of the columns to the list formula I get the below error..

"You may not use reference operators (such as unions, intersections, and ranges) or array contrants for Data Validation criteria"

This is what my formula said..
='Fixture Glossary'!$B:$B,'Fixture Glossary'!$E:$E,'Fixture Glossary'!$H:$H,'Fixture Glossary'!$K:$K,'Fixture Glossary'!$N:$N


See More: How do make a validation list with multiple columns

Report •

✔ Best Answer
August 29, 2016 at 13:25:02
OK, I wrote this formula to create a single column of data from 3 separate columns of varying lengths. I wrote it for your Columns B, E & H. You will need to extend it to add Columns K & N:

=IF(B1<>"",B1,IF(INDIRECT("E"&ROW()-COUNTA(B:B))<>"",
INDIRECT("E"&ROW()-COUNTA(B:B)),
IF(INDIRECT("H"&ROW()-SUM(COUNTA(B:B),COUNTA(E:E)))<>"",
INDIRECT("H"&ROW()-SUM(COUNTA(B:B),COUNTA(E:E))),"")))

If you put this in Row 1 of an empty column, it will grab all data from Column B until it runs out and then grab data from column E until it runs, then grab data from Column H.

You will note that each time it moves to the next column, you have to SUM the COUNTA from all the previous columns. It uses that SUM to get itself back to Row 1 of the current column.

As long as you drag that down far enough to exceed the total number of pieces of data in all 5 columns, you will have a single list that can then be used for your drop down.

You may need adjust for Column headers, etc.

Let's say we put that formula in Column P...

Your Source formula for the Drop Down can either include the entire Column e.g. P:P - but that will include any blanks at the bottom of the long list - or you can use the same concept to have the Source list stop at the bottom of the actual data in the long list by using this as the Source:

=INDIRECT("P1:P"&SUM(COUNTA(B:B),COUNTA(E:E),COUNTA(H:H)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
August 29, 2016 at 08:26:32
I'm a little confused by what you are trying to accomplish. Are you trying to produce one long drop down using data from 5 different columns or are you trying to create a drop down that builds "single line" choices from data in all 5 columns?

Here's a 2 column example. Which result are you looking for?

        B            E
1      Red         Shirt
2      Blue        Pants
3      Green       Tie

Do you want this:

Red
Blue
Green
Shirts
Pants
Tie

or this...

Red Shirt
Blue Pants
Green Tie

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 29, 2016 at 08:58:31
I am looking for the first option..

Red
Blue
Green
Shirts
Pants
Tie


Report •

#3
August 29, 2016 at 09:33:18
I don't know of any way to build a drop down from multiple ranges within Excel itself, but it can be done with a macro.

This code will loop through the data in your specified Columns and build a list (myList) based on the contents of each cell. It will then create a Drop Down in Sheet1!A1 and populate it with the data.

You will probably have to change the reference to Sheets("Sheet1").Range("A1") to match the location where you want the Drop Down.

As written, this code must be run each time you make a change to your data. It could be automated if that is something that would be useful to you. The code could be set up to run whenever a change is made to any of the source columns.

Sub CreateDropDown()

'Loop through Columns B,E,H,K & N
  With Sheets("Fixture Glossary")
   For col = 2 To 14 Step 3

'Determine last row with data in current column
     lastRw = Cells(Rows.Count, col).End(xlUp).Row
    
'Loop through rows, building list for Drop Down
     For rw = 1 To lastRw
       myList = myList & Cells(rw, col) & ","
     Next
   Next
  End With

'Place Drop Down in Sheet1!A1
    With Sheets("Sheet1").Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=myList
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 29, 2016 at 12:26:29
Thanks for trying.. I will just have to put everything into 1 column then.. Make it harder to scroll through but better than the macro option :)

Report •

#5
August 29, 2016 at 12:30:05
Why is it better than the macro option, especially if you automate it?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
August 29, 2016 at 12:35:19
It is a really large shared file and the more complex I make it the harder it will be to maintain and keep from getting corrupted..

Report •

#7
August 29, 2016 at 13:25:02
✔ Best Answer
OK, I wrote this formula to create a single column of data from 3 separate columns of varying lengths. I wrote it for your Columns B, E & H. You will need to extend it to add Columns K & N:

=IF(B1<>"",B1,IF(INDIRECT("E"&ROW()-COUNTA(B:B))<>"",
INDIRECT("E"&ROW()-COUNTA(B:B)),
IF(INDIRECT("H"&ROW()-SUM(COUNTA(B:B),COUNTA(E:E)))<>"",
INDIRECT("H"&ROW()-SUM(COUNTA(B:B),COUNTA(E:E))),"")))

If you put this in Row 1 of an empty column, it will grab all data from Column B until it runs out and then grab data from column E until it runs, then grab data from Column H.

You will note that each time it moves to the next column, you have to SUM the COUNTA from all the previous columns. It uses that SUM to get itself back to Row 1 of the current column.

As long as you drag that down far enough to exceed the total number of pieces of data in all 5 columns, you will have a single list that can then be used for your drop down.

You may need adjust for Column headers, etc.

Let's say we put that formula in Column P...

Your Source formula for the Drop Down can either include the entire Column e.g. P:P - but that will include any blanks at the bottom of the long list - or you can use the same concept to have the Source list stop at the bottom of the actual data in the long list by using this as the Source:

=INDIRECT("P1:P"&SUM(COUNTA(B:B),COUNTA(E:E),COUNTA(H:H)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Ask Question