CODEWITHSUNDEEP
excel
MJB
MJB

Reputation: 873

how to use combination of Table columns in data validation

I have a named table "Table1". Column Name holds names, column Surname holds surnames. I want the values from the column Name to be available in drop-downs. For some weird reason when I use formula 

"=Table1[Name]"

it returns an error, but when I create a named formula

rngNames = "=Table1[Name]"

I can use it as Source for data validation. I don't understand why it doesn't accept plain formula, but needs to be wrapped in the named formula, but at least I got this part working.

The next step I wanted to do is to have "Surname, Name" available in drop-downs. I don't want to (can't) add an extra column in the table. What I did is I created another named formula 

rngFullNames = "=Table1[Surname] & ", " & Table1[Name]"

In the Name Manager window it seems to work - I can see a list like this in Values column:

{"Smith, John"; "Black, Joe",...}

But when I try to use it in Data Validation it returns an error. Any suggestions how I can create the drop-down data validation from two table columns without explicitly creating an extra column to combine the two?

Upvotes: 2

Views: 1012

Answers (2)

Oliver Leung
Oliver Leung

Reputation: 812

Data validation does not support combined columns.

One minor thing. If you want to use a table column in Data validation, use it with INDIRECT(), i.e.:

=INDIRECT("Table1[Name]")

Upvotes: 0

Pierre44
Pierre44

Reputation: 1741

The Data Validation cannot work with different lists/Ranges as induced in the excel error message: The list source must be a delimited list, or a reference to a single row or column.

However you can make it work with VBA: 

Sub DataValidation()

Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row


Dim MyList() As String, k As Integer
ReDim MyList(LastRow)
For k = 2 To LastRow
MyList(k) = CStr(Range("A" & k).Value & "; " & Range("B" & k).Value)
Next k

With Range("L2").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Join(MyList, ",")
End With

End Sub

In my code, the Data Validation will be input in L2, the Column with Surnames is from A2 to the last row and the column with the Names is B2 to the last row.

Tell me if it works and I ll be happy to adjust it.

Note: Somehow, it doesn't work with a ",". Using a "," will put the names and surnames below each other instead of together. This is not the only issue in the normal Data Validation though.

Upvotes: 1

Related Questions