Allow multiple entries in Data validation

[hcabs99]

Hi All

Is there any way you can allow multiple entries to be selected and shown using data validation (or something else)?

I have a spreadsheet which i have lifted off a list in sharepoint for some external users who do not have access to our network. The Sharepoint list allows the users to make multiple selections from certain cells and i would like to be able to do the same with the excel sheet

Many thanks

Phil

 

[Ruddles]

I think what you're describing is a ListBox with its Selection Type set to Multi or Extend.

Create a ListBox by clicking Developer, then the crossed hammer-and-spanner Insert icon, then select a ListBox and click-drag-drop it on your worksheet.

Is that what you're looking for?

 

[hcabs99]

Many thanks, how do i select Developer? I am in Excel 2007

I think what you're describing is a ListBox with its Selection Type set to Multi or Extend.

Create a ListBox by clicking Developer, then the crossed hammer-and-spanner Insert icon, then select a ListBox and click-drag-drop it on your worksheet.

Is that what you're looking for?

 

[mikerickson]

Excel does not allow multiple entries in the same cell, but it does allow a single delimited string to be put in a cell. VBA code would be needed to translate the users (multiple) selections into a delimited string.

Also, multiple data in a single cell causes headaches, so splitting each of the choices into its own cell would be desirable.

 

[Ruddles]

Everything Mike said - agreed.

After you've allowed your user to select multiple values from the list, what do you intend to do with them? Place them in a single cell? Multiple cells? Something else? Any way, you're going to have to use VBA.

Developer is in your menu bar to the right of View. If it's not there, click the Office button > Excel Options > Popular > Show Developer tab in the Ribbon.

 

[hcabs99]

Everything Mike said - agreed.

After you've allowed your user to select multiple values from the list, what do you intend to do with them? Place them in a single cell? Multiple cells? Something else? Any way, you're going to have to use VBA.

Developer is in your menu bar to the right of View. If it's not there, click the Office button > Excel Options > Popular > Show Developer tab in the Ribbon.

Single cell, yes. i want to be able to put the data back into my sharepoint list which already has multiple entries.. I think, i will do something simple using concatenate before uploading. Thanks for all the advise with this both!

 

[karthik pullamsetty]

Hi Mike,

can you please help on this issue , I want to select the particular values in the data validations present in the cells b1 and b2 , based upon the selections there will be data change in the below rows here i need to paste the values in another sheet please help on this

Regards
Karthik

 

[mikerickson]

If you have a cell with cell validation, it can be filled by either choosing from the list, typing a list item into the cell or with a VBA command like someCell.Value = "x".

How did you want the user to select the values for B1 and B2?

 

[epsukas]

Here are simple videos and VBA codes for data validation with multiple selection.

regular dropdown list multiple selection for whole page https://www.youtube.com/watch?v=n1y9GZH4Kp4

Data validation drop down list with multiple entries without duplicates, VBA code for spesific column https://www.youtube.com/watch?v=k2BzCaQo06g

Data validation drop down list with multiple entries without duplicates. Reselecting from dropdown list deletes previously selected items. VBA code for spesific column https://www.youtube.com/watch?v=cRpTzOnaf48

 

[sbellison]

Thank you for the very good videos! However, this is not really what I'm looking for. I'm not trying to use a data validation list in this example; rather, I need to allow the user to enter any value and only raise a warning if the value intersects a timeframe that has been entered earlier in the column. For example, in the spreadsheet visible at Imgur: The most awesome images on the Internet, when the user entered the value '0800' in cell G31, and '1230' in G33, the data validation should throw a warning because these times have already been entered in the time ranges earlier in the column.

I don't understand how the videos you linked can help with this problem. Is there another way to solve this?