Excel Data validation list from single cell

Question

I would like to create in a single cell a list to use for data validation.

For example:

Single cell:

| 1-3,6,8 |

Data validation list:

can you clarify please, you have in one cell, say B1, value | 1-3,6,8 |, and then, using B1 you need to create dropdown for another cell, say A1? — Dmitry Pavliv
– Dmitry Pavliv, Commented Feb 14, 2014 at 10:57
if you assign a data validation from a list of values to a specific cell, when that specific cell is selected a dropdown list is shown with the possible values selected before. instead of a list from a range of cells i would like to create that same list but from a single cell with the value, for example, 1-3,6,8 that would create a list containing the values 1,2,3,6,8 — randrade
– randrade, Commented Feb 14, 2014 at 12:01

Dmitry Pavliv · Accepted Answer · 2014-02-14 12:15:31Z

1

Yo can try this:

Sub DataVal()
    Dim x As String, v, v1, i As Integer, j As Integer, s As String
    x = Range("C1")
    v = Split(x, ",")
    For i = LBound(v) To UBound(v)
       If InStr(v(i), "-") <> 0 Then
          v1 = Split(v(i), "-")
          For j = v1(LBound(v1)) To v1(UBound(v1))
             s = s & j & ","
          Next
       Else
          s = s & v(i) & ","
       End If
    Next
    s = Left(s, Len(s) - 1)
    With Range("D1").Validation
       .Delete
       .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=s
    End With
End Sub

I assume you want to apply data validation to D1 cell and get the values for that data validation in C1, change at will

edited Feb 14, 2014 at 12:15

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

answered Feb 14, 2014 at 12:01

CRondao

1,9012 gold badges12 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brandon R. Gates · Accepted Answer · 2014-02-14 11:07:42Z

0

Write a custom VBA function. Very pseudo-code follows:

function validateCell(strValues as string, rngValid as range) as boolean
  validateCell = true 'will return true unless the following loop finds an invalid value
  for each value in strValues 'this will require some parsing of strValues parameter
    if value is not in rngValid 
      then validateCells = False
      exit function
    end if
  next value
end function

answered Feb 14, 2014 at 11:07

Brandon R. Gates

3181 silver badge7 bronze badges

2 Comments

randrade Over a year ago

this code you gave me, will only validate the value, I'm sorry i didn't explained myself correctly, what i would like is not only to validate but also create a dropdown list when the cell is clicked with the only possible values. is that possible?

Brandon R. Gates Over a year ago

Ohhhh I get it. You want to put 1-3,6,8 in a single cell to use as the validation list? Write a function that loops from 1 to <max> value, and parses your input cell. Stick the values that belong into your validation range. If all the input cells use the same rule, you're golden.

Collectives™ on Stack Overflow

Excel Data validation list from single cell

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related