6

I need a simple way of taking a comma seperated list in a cell, and providing a drop down box to select one of them.

For Example, the cell could contain:
24, 32, 40, 48, 56, 64

And in a further cell, using Data Validation, I want to provide a drop-down list to select ONE of those values

I need to do this without VBA or Macros please. I want this to work with Excel 2010 and later.
I have been playing around with counting the number of commas in the list and then trying to split this into a number of rows of single numbers etc with no joy yet.

Improve this question
2
  • Would you mind adding the software you would like to do this operation? Assumptions can lead to less than accurate responses.
    – Fergus
    May 27, 2014 at 3:43
  • I don't think you can do it this way... I think you'll have to split the values up (using Text To Columns) and push the results on another work sheet. And then use the Validation Tools -> List based upon the other worksheet. Naturally, this has issues, such as not being able to easily make changes in the future.
    – Dave
    May 27, 2014 at 8:34

2 Answers 2

Reset to default
2

You're on the right track with using the commas to identify the individual numbers. This works for 50 comma separated values - if you're in Excel 2010 this can be expanded to 8000ish items (although for that many I'd probably reconsider if this is the best way to handle the problem :)

Let's say your list of numbers are in cell A2.

  1. If your list of numbers has spaces after the commas, either do a find and replace to remove them, or do a substitution in cell B2: =SUBSTITUTE(A2," ","").

  2. Now let's find all the commas. In cell C2, enter =SEARCH(",",$B2,1). This finds the position in the text of the first comma.

  3. In D2, enter a similar, but slightly different formula to find the next comma: =SEARCH(",",$B2,C2+1)

  4. Copy this across for (let's say) 50 columns. This will take you out to column AZ.

  5. Now we can grab each individual number. In cell BA2, enter =IFERROR(LEFT(B2,C2-1),""). This grabs all text to the left of the first comma or returns nothing if the cell is empty.

  6. In BB2 enter =MID($B2,C2+1,D2-C2-1) to pull out the next number, or return nothing if there is no number in this position.

  7. Copy BB2 across for 50 columns, this should take you to column CX.

  8. Now you can assign your cell validation to the range BA2:CX2, and each number will appear in your dropdown.

enter image description here

Improve this answer
0
0

In Office 2010, I was able to embed a numerical list for data validation without the use of a spreadsheet column by typing this: 

=0,1,2,3,4,5

Depending on your needs and if you use the right syntax, you may not need a spreadsheet column at all.

Improve this answer
1
  • 3
    Where did you type the list?
    – Excellll
    Mar 6, 2015 at 23:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .