Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Multiple List Values In One Cell

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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

View Answers     

Similar Excel Tutorials

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 ...
Make All Numbers in a List Positive in Excel
Take a list of numbers and make them all positive, regardless of whether the list contains a mixed set of positive ...

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 have a validation on one of my columns for Users to select values from a
list in a different worksheet. This works fine, but now the Users need to
select multiple values in the one cell. How do you do that? A simple
Pet Type
Dog Retriever, Beagle

Good mornngs,

I've searched for a topic to cover this but could not find one so....

Is it possible to restrict the values of cells in a particular column to entries defined in a list BUT to allow each cell in that column to display multiple items from the list (seperated by a comma for example)

I've attached an example of what I would like to do -

In Sheet "2010 Data" I want to be able to select multiple values in column F....(the values are defined within the list named "Platforms" on the worksheet called "Lookups"

Thanks in advance !


I want to be able to use a selection from a drop down list to determine the values for another drop down list:

If I have the above data I want to select "Yellow" from the drop down list in B8 and then have the list for cell C8 be the values in C2:C5. Or if I select "Red" in B8, the options in the drop down for C8 would be the values in D2:D5. I need to do this for multiple rows using the same data table so that I could get to the data above by users selecting all the data from drop down menus.

I'm open to any complicated solution using macros, VB controls, names, or any combinations in Excel. I'm using Excel 2003 and Windows XP.

To explain what we are trying to do as simply as possible:
We want to create a list of various suppliers contact details. In one cell per row, we would like to be able to create a 'list' of categories (ie. Landscapers, Carpenters, Builders, Earthmovers, etc) where we can select MULTIPLE valves (ie. Landscaper and Earthmover) and we want to be able to then filter that information to display only 'landscapers' etc....

Is there a way to create a 'list' (ie. drop down or similar?) where you can 'select' the multiple valves for ONE cell?

Is there a way to be able to do this? (FYI. We know how to filter etc, we're just looking for guidance on the multiple valve lists)


Hi Team,

I have problem with the data validation list, to select multiple (more than one value) in the list and then based on the the selection below cell should reflect the values of the values selected multiple.

Gone through the link, but for my solution no where found, hence requesting.

I am not good at English, so, don't mind if the above is not understood.
Please find attached the file which explains in detail.

Thanks & Regards

Hi All,

This might seem like a redundant question but I checked a lot of answers but nothing seem to work.
I created a data connection using MS Query trying to query in SQL Server. It is not a complicated query that I am using. The query is trying to select 5 columns from a table. In the where clause I have a parameter which needs to select multiple text values from one/multiple cell.

So consider this as the query:
select a1, a2, a3, a4, a5 from table1
where a6 in (0,1)
and a7 in ([param1])

The values I have in excel are something like below.
abc cde
efg ghi

and so on.

The param1 is dynamic in both number of values and the value itself.

I tried using the parameter value as Equals and well as Is One Of.
When I try using one column it works fine but If I concatenate the column to use values like "abc cde,efg ghi" it doesnt work. Ofcourse it does not allow me to select a range of columns.

Thanks in Advance for any help.


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,

Dear expert

I have set of 5 column values multiple repeats values in every column..

i want create drop down list for each 5 column , get values list depends upon values select in first dropdown list, like e.g





i want apply this condition in sheet "master"

please find the attachment....

Hello - hope someone can help me.

I am using data validation to allow the user to select from a drop-down list in a worksheet. I want them to only be able to select from the list and not to input any other values, and I have checked the box in Error message which says 'Show error alert after invalid data is entered' but I am still able to type in an invalid value without the error message appearing.

The named range which informs the list is large and includes lots of blank cells to enable me to add extra values to the list without having to rename the range, could this be the problem?


I am stuck up on a little problem and I thought the experts here will find a way

In short I want to create interactive charts in the sense that I want the user to make a random selection from a drop down menu or a list. I am attaching a sample file with the required columns, so please refer to it.

I will have data for seven days for each week in the 'DATEA' column along with a 'TIMEA' column which I want to depict in the x-axis of the graph. I want to plot the three columns 'PPS', 'MAX(A.NTDMPCH_1)' and 'SUM(A.NTDMPCH_3)' in a standard bar/line graph with the condition that the user selects the value in the 'BSC_NAME' column from a list. Over here there is only one distinct value in the BSC_NAME column but in the final data there will be many more and I want to give the user the ability to select whichever value he wants to display. There is an additional column 'LAC' and for different values in the 'BSC_NAME' column there will be different values. However it is possible that a single distinct value in the BSC_NAME column has multiple different values in the 'LAC' column as is the case in the attached file. What I want the user to have is the ability to select the value in "BSC_NAME' column and the chart dynamically plots the three columns mentioned above against each distinct value in the LAC column for the seven days.

Thank you.


I need to set up an excel sheet with dependent data validation that is multiple levels deep.

ie. Select value from dropdown list in Col A returns dependant list in Col B, select value from the list in Col B returns list in Col C etc.

Is this possible?

Also, is it possible to set multiple values to return the same list? ie in the drop down you have 4 items. Each returns a dependent list but items 1 and 3 both return the same dependent list. Just saves me making many copies of the same list with different names which isn't a huge issue.

Really appreciate any help you can provide.


I need to set up an excel sheet with dependent data validation that is multiple levels deep.

ie. Select value from dropdown list in Col A returns dependant list in Col B, select value from the list in Col B returns list in Col C etc.

Is this possible?

Also, is it possible to set multiple values to return the same list? ie in the drop down you have 4 items. Each returns a dependent list but items 1 and 3 both return the same dependent list. Just saves me making many copies of the same list with different names which isn't a huge issue.

Really appreciate any help you can provide.

Sorry if this is a repeat. I read through many posts and do not see the same exact question. I am familiar with the generals of excel, but not when it comes to macros.

First my pulldown list question. How can I allow users to select multiple values? They can only select one, but, for example, I may need them to select four names from the pull down list. There are no actions following that.

Now for the macro. I want to create a macro that when X number rows are manually added to tab CC, that
- Column A from tab CC is copied to Column A on tabs AA and BB.
- For the new rows (X) on tab AA that now only have column A filled in, copy columns B-U (the vlookup functions) on tab AA to the new rows (X) on tab AA.
- Repeat the step for tab BB, for columns B-K.

I can't send the worksheet because it has sensitive information.

Thanks in advance so much!!


Hello all

I am working on an SQL Query generator which will insert a list of values into an SQL Query within excel, for my to Copy to my SQL Server Management suite to insert the values into a table.

So, I have a list of values which are pasted into Column A. Column B contains a formula to create a query to insert Column A into SQL.
It is a simple formula,
=IF(LEN(A2)=7,"INSERT INTO #MyTable VALUES('"&A2&"')","")
Which works great and I can copy the formula result directly into SQL and everyone's happy. However;

I will have between 1 and 100,000+ records to do at once. So I have this formula in ALL of Column B. I would like to be able to Select Column B and Copy, then paste directly into SQL without blanks. As it is right now, I get a million blank rows. The query still runs just fine but I would prefer if the blanks didn't get copied. There are no blanks in the middle of the data, if I have 30 records, row 31 to row 1048576 are the blanks I am referring to

Is there any way to only select cells within a row that contain values?

I have tried Copy/Paste Special, then use End-Mode on row 1, hold shift and press down arrow, but it still selects and copies every cell.

I am open to VBA solutions.


I have a 5000 line table I am filtering by a few columns, and I'd like to calculate an exponential trendline value.


But I actually don't want all the values from R1059 to R1167 - I want to select only the displayed values (R1059, R1068, R1077, etc). Is there a way to select only display values to use in a formula? The problem is it would be a lot of manual work to select them all - there are 50 or so instances I would have to select 13 manual values.

I am using Excel 2007 on XP.


I have a ListBox from the Control Toolbox on an Excel sheet. I wish to select multiple selections from the list box and for these to be displayed in a cell on the worksheet. I.e. My listbox is populated with the numbers 1 to 7. I select, for example, 2 and 6 and I want these to be displayed in cells A1 and B1 respectively.

I have been playing around with the Linked Cell property, however can't seem to get this to work. I have seen a couple of examples using a Command Button to place the value in a worksheet, but ideally i would like the sheet to be populated with values as and when I select them in the list box.

Any Help on this would be appreciated.

Many Thanks,


"You can't carry out this action at the present time"

I'm currently getting this cryptic error when I try to run the following command:

DoCmd.RunSQL ("delete * from T_Reason")

where T_Reason is a table with 2 text fields, Territory and Reason. There is 1 record of dummy data in the table (it will be populated later in the VBA function.

I've tried to remove this line (I can clear out a temp table by myself if necessary), but then later in the code, I get the same error on this line:

DoCmd.RunSQL ("INSERT INTO T_Reason ( Territory, Reason ) " & " SELECT " & Territory & ",'" & hold_reason & "'")

which is kinda the pivotal line in my function. Has anyone else come across this?

FYI, the entire function is:


Public Function concat_Reason(Territory)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim hold_reason As String
Set db = CurrentDb
hold_reason = ""

' clear out old list

DoCmd.RunSQL ("delete * from T_Reason")

' start creating new list

' select list of records for this patient

Set rst = db.OpenRecordset("FtrAdj")

' skip process if there are no items in the list

If rst.BOF Or rst.EOF = True Then GoTo jump_out

' start Concatenate Multiple Records to Text Field

Do While Not rst.EOF
If hold_reason = "" Then
hold_reason = rst!Reason
hold_reason = hold_reason & "; " & rst!Reason
End If
' end Concatenate Multiple Records to Text Field
Set rst = Nothing

' load the concatenated list

DoCmd.RunSQL ("INSERT INTO T_Reason ( Territory, Reason ) " & " SELECT " & Territory & ",'" & hold_reason & "'")

End Function

Is there a quick solution for inserting a new line using a command button.

I'm standing on different lines in the excel sheet. When pressing the command button on top of document, I can insert line under the active cell. I know i can use a keyboard shortcut, but I will populate this line with other information as well (that I have figured) Recording the macro gives me absolute values: Range ("A6").Select I would like something like this:, insert row beneath this row.

any suggestions?

Hello. Ive been searching for 2 days and cant seem to find anything to make this work for me. I received data that I need to clean up, so I can load it into a data base. The biggest issue, is that one cell contains multiple values, which I am prepared to insert by hand, unless someone knows how to fix that as well. As for the other issue, I would like for a VBA to look at column A, and insert that value - 1 rows below the line and then copy the line to the new blank rows. Here is an example

qty serial type location 2 123, 124 A1 San Fran 20 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20 B23 LA 4 001, 002, 003, 004 C12 NYC 3 23, 93, 93 R Vegas

So in this example, line 2 would receive 1 extra line under it, and copy the rest of the line to the new row. The serial column is the one that I will more than likely have to copy by hand, but that wont take long once i have the proper amount of rows. THanks for the help.

not sure if it matters, but some of the other VBA's ive tried have error'd...
im using Excel 2007

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?

Hi All,

I am new to this forum so be patient with me....

I am using Excel 2010.

I know how to create a drop down list - see attached.

The first drop down list shows the horizontal values on "Food List" tab A1 through to I1.

What is want to do is this:

* When I select the first drop down list it shows me the horizontal selection.
* When I select the choice i.e. "Grains", it shows a second drop down list showing all of the vertical selection underneath that heading, i.e. C2 to C9.

How do I create this second drop down list?



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.


hey all...I do some data validation. Suppose I have an LOV and I create a list using that LOV. What I really want to do is creating a list that I can select multiple values using, for example, checkboxes and still be able to run a report. I suppose I could create a combobox control but, frankly, my experience is limited there and I have to distribute these instructions to others. I was hoping there might be a way (formula or data validation technique) that would allow me to create that list and allow for multiple selections such as checking a box next to each item I want to (check box instead of radio button, for example).
Is it possible or I am stuck with a combobox control?


Let me explain the challenge i'm facing now.
I have 4 comboboxes on a userform. Each has the same set of values assigned to it when the form starts up.
Let's say the list of values are

When i select 1 in combobox 1, this should happen
list in combobox 1 - 1,2,3,4,5
list in combobox 2,3 and 4 - 2,3,4,5

Following from that, let's say i select 2 in combobox 2, this should happen
list in combobox 1 - 1,3,4,5
list in combobox 2 - 2,3,4,5
list in combobox 3 and 4 - 3,4,5

And then selecting 3 in combobox 3,
list in combobox 1 - 1,4,5
list in combobox 2 - 2,4,5
list in combobox 3 - 3,4,5
list in combobox 4 - 4,5

and so on.

Right now. I'm racking my brain over this and i can't seem to get the right solution. Feed me with ideas please!!

using xl2003

Shie Boon