Multiple List Values In One Cell

Not sure if this is possible?

I have a table in which you can select values from a pre-defined list

However on some days I need to be able to select multiple values from the list and not just one value

Is it possible to do this or if not is it possible that on the days I need to select multiple values I can get excel to auotmatically insert a line to enable me to select the second, third etc value without having to physically insert the extra line?


I have attached a basic table showing what I need to do

Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

Similar Excel Tutorials

Guide to Combine and Consolidate Data in Excel
Guide to combining and consolidating data in Excel. This includes consolidating data from multiple cells, multiple ...
List All Defined Names and Values in the Worksheet in Excel
Quickly list all Defined Names and their Values in the worksheet without using a macro. This allows you to view an ...
Make Negative Numbers Positive in Excel and Vice Versa
I will show you a few ways to change negative numbers to positive numbers and back again in Excel. Don't forget to ...
Count the Number of Unique Values in a List in Excel
Use a formula to count the number of unique values that are contained within a list in Excel. Steps to Count the Nu ...

Helpful Excel Macros

Get Values from a Chart
- This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
List All Named Ranges in Excel - Displays the Name and Value for Every Named Range Within the Active Workbook in Excel
- List all of the named ranges in a workbook in Excel and the corresponding values stored within every named range. This

Similar Topics

I'm not proficient with VBA and would like some help with a recurring stumbling block.

Is there a way to have Excel read in a list of values and then select those values from a pivot table field? The list and the pivot are on separate worksheets in the same workbook.

The list of values is usually the result of some other manipulations. I want to be able to go back to a pivot table and have those values selected programmatically because the list can have 100 or more items.

(Excel 2003)

Hello all,

I have been racking my brain with this problem for the last few days and alas I have been left confounded with this problem and have no solution.

I want to create essentially a recipe spreadsheet by where users are limited in their choices that they put in the ingredients box for a predetermined list. The trouble is I have over 2000 different ingredients from which users can select.

I want to create a drop down list that is progressively filtered in the one column by the typed values inputed by the users. Ie when user enters s, all values beginning with s come up. Then the user types u so becomes "su" only showing values begging with "su" and so forth.

I am aware that the drop down list automatically jumps to the values on the list by the users inputting the first letter. But because of the large amount of values I have for example over 140 values for "s" this will be quite slow for users to scroll through and select. Also after reading on this site is that correct that the list function is only limited to 1000 values?

The second problem is that the values for the list will be located in a separate worksheet.

I am presuming that this function will require the programming of a macro as I don't think excel has this functionality built in any assistance with this would be greatly appreciated.

Thanks again,

Prior to Excel 2007, when you did a "Select Multiple Items" on a drop down list (Report Filter) and chose only the items you wanted seen in the list, users would see "(Multiple Items)" in place of "All". When the clicked on the drop down list, the items they would see would then be ONLY the items that were chosen to be seen. Now, in Excel 2007, the same thing happens, but when users click on the drop down list, they see a check box next to all the items, and are not able to use the field any more for filtering. For example:
Report filter has 5 values. I chose "Select Multiple Items" and I say only show items 1 through 3. When users now go to use that drop down list, they can not simply click on "3" to eliminate 1 and 2, because all that will do is uncheck the check box.
I am looking for the multiple items to basically work more like pre-2007 version in this case.
Any ideas?

I am running to a snag when i tried to create a conditional statement and a List Box within a single cell. In the column before I have a List Box that will allow me to select between 5 different values. In the following column i set up a conditional statement so that depending on a specific the item selected in the List Box a number displays.

for Instance:
In C1 List Box has A,B,C,D,X
In D1 I have the following conditional statement: =IF(C1="A",7,IF(C1="B",1,IF(C1="C",5,IF(C1="D",5,""))))

I have a value displayed in D1 if A-D is selected, but if X is selected from C1, I would like to have a drop down appear with values 1 through 5 for the user to select. I was able to get this done by just creating a List box in D1 to have values 1 through 5 and have the if statement in the formula bar. However if I select X and select a value from the drop down and then decide to select A instead I no londer have the IF statement to populate the D1 cell.

any ideas on how I can solve this problem.


I have a SharePoint list with a Choice field that will allow multiple values. I would like to be able to analyze the data in an Excel pivot table.

There are no problems exporting the SharePoint list to Excel, however when the Choice fields (that permit multiple values) are exported they are placed in a column with the multiple choices separated with ";#".

For Example:
Products Capabilities
Shovel Dig;#1;Bury;#2;Cover;#3
Rake Dig;#1;Cover;#3
Hammer Dig;#1;Hang;#4
Pitchfork Cover;#3

The functions are not mutually exclusive and a product may have one or many capabilities.

I am looking for the best method for normalizing the multiple-value fields so I can analyze the data using a pivot table.

Any suggestions would be GREATLY appreciated.


I am using Excel 2000 on a Win XP machine.

Is there a way to use a type of drop down menu, but be able to select more than one text value to populate into each cell?

I have a list of approximately 30 properties we used to describe documents and I would like to see each in the drop down menu or list, but be able to select one or more than one to associate with each document in the system (over 1000). Each document is associated with a set of these properties that is likely, but not necessarily, unique.

Is there a way this could be set up in excel, or is this a functionality that doesn't exist?

Thanks in advance!

This is my first post, so I would first like to say that all the information I have found on this board has been extremely helpful. Thank you.

The issue:

I have a workbook that gets data from a text file, that data then populates a pivot table. The pivot table has one filter critiera (portfolio). One the filter is selected, the data populates an output tab with various formulas, values, extra. That output gets copied and pasted the various tabs within the workbook. Go back to pivot table and select different filter, rinse and repeat for 168 critiera (portfolios)

I currently have macros for all the steps in this process except selecting the pivot table filter critiera. I only need to select the filter critiera one at a time (no multiple values).

I am not a programmer, I have a minimumlistic scope when it comes to vba, however my non-vba excel knowledge is expansive. Thank you for your assistance.


I have a list created on a separate sheet in my workbook named List. On 2 other worksheets (Day Log, Night Log) I want to be able to select multiple items from that list. The list is employee names and it is labeled Operators. My drop down list is on Day Log cell B2 and I would like to be able to select 1 or more names and have them in the same cell, separated by a comma. Any help would be appreciated.

Thanks so much!

Hello All,

Column A has values which may or may not be unique (several cells in Column A may have the same value)
I want to populate Column B with these values, but I do not want to have repeated values, or empty spaces between the values, in Column B (see attached).

That's the meat of the issue, I can populate Column B with Column A values easy enough, but I'm having trouble getting rid of the repeated values and "collapsing" Column B so that it looks like in the attached xlsx.

So, that's the basic question I need answered. But, ultimately, I want Column C to allow a List of specific values (see attached). The allowed values will be defined by the values in Column A. However, I can't just point the validation straight to Column A, because then the in-cell dropdown in Column C would be extremely long and littered with duplicate values as well as empty spaces.

So, my first thought was to just populate Column B with a nice concise list of the values in Column A, and then use Column B as the Data Validation source. But, I'm open to a more elegant solution if someone has one; otherwise, just getting Column B to contain just the short list of values will be fine.


ps. I don't really care about the order of the values in Column B, just as long as it's one after the other.

Hey there,
I have a list box where users can select one or more values. Those values are stored in a text box separated by a comma. I have a query in which I need the criteria of the query to find a value if it is in the text box.

e.g. ReceiverNo (1234, 12345, 123) [Values stored from list box selection]

How do I use the criteria field of my query to look in that ReceiverNo field?

This is what I tried: In([Forms]![frmReconciliation].[ReceiverNo]) That doesn't seem to work?

Any ideas or other suggestions?????

As always, any help is greatly appreciated!

I have an Excel application, where the user can browse to a folder and
process all the documents in that folder. I use the Office 10
FileDialog object (FolderPicker) to allow the user to do this with an
easily recognizable dialog box.

The problem is, the users would like to select multiple folders at a
time (which FolderPicker does not allow).

I could allow them Browse and select a folder, add it to a list, then
Browse again and add to the list, then Browse again, but that would be
a long tedious process.

Is there anything that would allow me to select multiple folders at a
time, similar to how you can select multiple files at a time?

Thanks for any suggestions,

I need to search a column containing multiple Regulation Numbers (including duplicates and multiple Regs in single cells)(Column A) to find any Regulations listed in a separate column (Col B) and lookup a Document ID from a third column (Col C) and return them in Col D. Col D should not have duplicate results.
Col A Reg List
Col B Search List Col C Doc List Col D Result 1.1.1
3.1.1 1.1.1 A A 2.1.1 1.2.1 B D 2.1.2 C F 1.1.1 D 1.1.2 E 1.2.1
3.1.2 F 1.2.1 D 2.1.1 A 2.1.2 B

In the example above search for all instances of 1.1.1 and 1.2.1 in Col A and return the applicable Documents from Col C without duplications in Col D. This example should find two instances of each Reg but as both have Doc D attached to them only reports D once.

Thanks in anticipation,



I am trying to create a drop down list that allows me to select multiple options within the list in Excel 2007. I know that you can do this with a Combo Box (Form Control) but the option to select 'Move and size with cells' (Format Control > Properties > Object Positioning) is grayed out and I need the list to move with the cell it is in.

With a Combo Box (ActiveX Control) you can select 'Move and size with cells' but I can't figure out how to get it so that I can select multiple options within the list. I thought that under 'Properties' for the ActiveX Control that I could select 'Check Boxes' under 'ListStyle' but I am only given the options of 'Plain' and 'Option Buttons'. Is there a reason I don't have the 'Check Boxes' option? Am I missing a step or am I on the wrong path?

Thanks a million for any insight you may have!

I'm trying to build a spreadsheet which charts annual snowfall of a particular ski resort and displays a graph showing the x values as days of the month (spanning a 6 month season), and the Y values as inches.
Problem I'm having is because the days of the month repeat (1-30, 1-31, 1-29, etc) excel wants to stack the graphs instead of create one long trendline which spans the whole 6 month season.
I want the x value data to show up as the day of the month but the only way I can create a single continuous line is to make the x values not repeat (IE 1-30, 31-60, 61-90, etc). I also want to be able to be able to correlate the series of days with the name of the month, something like:

1-30 1-31 1-31
Nov. Dec. Jan.

I feel like I need separate "dummy" x values; say 1-180 (in order to create the uninterrupted line) but have it reference and display the desired values of 1-30,1-31,1-31.
I don't know if this is correct or perhaps I'm going about this all wrong.

Which is why come seeking assistance

Hope what I described is not confusing...

I have a table that I copy and paste employee data into, then manually enter start and end times for a daily work schedule. I would like to have a macro that removes duplicates and keeps certain unique values. Essentially, I'll have a table with a list of names and shift times, then I'll generate a new list. Some of the names and shifts will duplicate, while others drop off, and new ones are created.

It's kind of hard to explain so I've attached an example spreadsheet with before and after tabs.

Any help is greatly appreciated.

Please help.

I have a pivot table with multiple fields and I want to sort the whole table by specific dates. I drag the date field to report filter and try to select multiple dates using the "select multiple date" check box.

It does not filter by the selected dates. It will select by all and will select 1 individual date but not by the check boxed dates.

the drop down field always says all when I select mutiple dates.

Any ideas?



I am a newbie with perhaps a very silly first question!

I am attempting to build a report with a graph where rather then use multiple check or option boxes to select and deselect criteria to switch formula on and off to graph, to use a Form control listbox (with selection type as "Multi") so that they can select multiple criteria from there to chart the information.

I have used a listbox from the Developer/Insert/Form Controls menu and have populated it with a list of 14 criteria to select from and checked the Selection Type as "Multi" so that multiple items can be selected to graph and after that this is where I become lost.

How would I go about getting the list box to populate the criteria selected within it into a spreadsheet in the excel workbook where I could then reference formula to the selections and bring back data to graph so I can make my graph dynamic?

Any help much appreciated.


I have a ComboBox control on my form (bound) that has two choices for the user to select. However, they are still able to type values other than the two I specified. The following properties are set:

Limit to List = Yes
Allow Value List Edits = No
Show only row source values = Yes

What am I missing?


I'm using Excel 2003 and am trying to filter a list of values. The list has duplicate values in it. When I use Filter>Advanced Filter and select "Unique records only" the filter only eliminates some of the duplicates. If there are 4 values of 100, it may filter 2 of them or none at all. Ultimately I'm trying to end up with a list of values with no repeats. Any help is appreciated.


Hi all! I am fairly new to Excel Macro and I am trying to figure out how to select a value from a drop down list using Macro.

Say, I have a drop down client list in cell A1. The list has 100 values, say Client1 Client2 Client 3...Client100. A pre-set exhibit is linked to the value in cell A1, so that every time you select a client from the drop down list, the values in the exhibit changes accordingly.

What I would like to do, is to have macro select the client value from the drop down list, one client at a time, for 100 times, and then copy and paste the exhibit in a new excel workbook and save. I can figure out the copy and paste part using macro recorder. BUT, not the selecting value from a drop down list. I tried using macro recorder, but it didn't seem to record my action?

Anyone has any suggestions? Thank so much in advance!!

Hello all,

I have created the attached matrix to help me priorities data collection of different datasets for a project I am working on.

At the moment the user needs to type in values to complete the form, what I would like do is have a combo box/drop down box in each white cell so that there are only two options that the user can select from. So for example in cell D3 the user could only select Schools or Churches.

Is there a way that this can be easily in VBA? I have tried to work my way round this using the Data Validation tool however it will not let me enter two cells in two different locations.

I have thought that it might be possibleto count back from the active cell to work out the values that need be added to combo box (using AddItem?) but can't quite get my head round to how this can be done.

I also looked at the solution here however I don't think I can use this as it uses the validation option (requiring a range) where as I need to enter two list values instead.

Any help that can be given would be appreciated.



I need a multiple select listbox to display in the cell as a list of all designated choices. I have the listbox created, but I need the VBA code to make it so that the listbox isn't visible until you click on it. I also need the VBA code so that once there are multiple selections, the selections will be displayed in the same cell as a list separated by commas that can be filtered. Can anyone help?

I'm trying to use a calendar in my UserForm that would allow users to select multiple days and then display these days. Has anyone done this?

I have found a site where the guy has done this in HTML:

Trying to write a basic macro here that will go through a column searching for blank cells, and merge them with the first activecell.end(xlUP). Here is an example of the data:


Here is my current code, again, very basic:

Sub merge()

For i = 1 To Rows.Count
If Cells(i + 1, 1) = "" Then
Cells(i, 1).Select
ActiveCell.Offset(-1, 0).Select
Range(ActiveCell.Address, ActiveCell.End(xlUp)).Select
   With Selection
   .HorizontalAlignment = xlCenter
   .VerticalAlignment = xlCenter
   End With

End If
Next i

End Sub

Current issue is it will merge the first "Americas" with the following blanks, then the next "Americas" and blanks, but then it Selects both "Americas" that have been merged and presents this msgbox "The selection contains multiple data values. Merging into one cell will keep the upper-left most data only." Is there a way to get it to not select the multiple data values and move on to the next line to search and merge?

Results in:
Americas (merged 3 cells)
Americas (merged 5 cells)
both of which are now selected so that if you press Ok in the msgbox it turns into "Americas" (8 cells merged) and executes the next merge that has blank spaces (repeats same issue)

I want to make data validation to certain cells, that instead of showing a list of available values, should show a list of pictures.
For example, now I have a list of countries, but I would like to show a list of country flags, where the user can select a flag picture from the list.

How can this be done?