Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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

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 ...
Make a Drop Down List Menu in a Cell in Excel
I'll show you how to make a drop down list menu in a cell in Excel.  This allows you to have a list appear when yo ...
Vlookup on Data with Spaces
How to use the Vlookup function when your data has extra spaces in it. This happens when the lookup data is import ...
Easily Compare Duplicate Values in Excel
Here, I'll show you a simple technique to quickly and easily compare large lists of duplicate values.  This allows ...

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.

Hello everyone,
I created a dropdown list with the validation tool, and i would like to link every number of my list with special values that i enter afterwards.
For example : my dropdown list has the values A, B and C. When i select A, i want to be able to write in the following right cases values (A corresponds to 10 hats, 13 shoes, etc..,)
And when i select B, it automatically hides the following right cases and i can add new values ( while staying in the same line ).
I'd also like to sum up all the values i added in the 3 different values A,B,C.
How can i do it?
I'm attaching my excel file.excel.xlsexcel.xls I would like each of my "Caisse" to have different CB values, cash and so on.

Thanks in advance,
Any help would be greatly appreciated.

I have an issue that I need some help with. I have a buttload of data (gets recorded multiple times per week) that must be entered manually into excel, this can be time consuming as you have to insert a row for every item that has to be entered. There are approximately 50 individual tables in the one single worksheet.

My question is this, is there a way to have a sort of programmed button that could be pressed that would insert all of the rows at once? Or perhaps a new line could be generated for each table with the only missing data being the values that would have to input manually?

To make it more clear I have attached a sample table. In the sample table, what I would want to do is insert a row at row 47 and at row 93. I want to do these at the same time easily. Keeping in mind that there are 40 some other tables arranged in series like that in one single spreadsheet. I cannot simply hit ctrl and select the two rows and then insert both rows, this would be equally as time consuming to do for all of the rows that would have to be added. Also, in the attached table, most of the values are either calculated values or are hard set numbers. The manually inputted values occur in columns B, E, and F. Everything else is copied down from the previous line.

If anyone has any ideas I will be all ears. Just ask if any clarification is needed as well.

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

I have userform1 & listbox1 that adds th selected value to the end of a list IN column A.
When i select add multiple values in he list box properties then no value is added. Can anyone explain how this function works, thanks Stevie p.

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've been searching the Internet for the past two days and couldn't find any solution... here is the problem...

I have a table with 3 columns and 4 rows (including headers on the first row) . I have to use combo box or list box and put all the headers (on the first row) in it so that when I click on a drop-down button it will show all the list of headers (text). Then, when select a header from the list, the values (on multiple rows) under that header will be selected automatically.

Then I would like to create 3 buttons to export all the selected values under that header (column) to SharePoint in 3 different files Excel, Word, and Powerpoint.

This way I need to send out 1 column at a time not the whole table.

Table example....

Tim, Joe, Wan
24 36 76
2 4 8
4% 1% 8%

Any help would be highly appreciated.

Thank you.

Hi guys,

So i have a bunch of questions - one per column, and a drop down list in each with valid values. But in some cases the user needs to be able to select more than one value, as two or more may be true.

How can i do that?


I am trying to create a graph where I can select multiple values from a drop down list and create a graph based on these values. Each value would contain a column of data that links to a specific date. I would also like to select a date range that can be changed by selecting values from a drop down list.

Hi, I have about 10 tables on a single spreadsheet, and everyday i need to insert a new line to each table, the added lines will never get deleted, it keeps adding on, currently I have a macro like this:

Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
End Sub

It doesn't work exactly how i wanted it, because once I add a line on the first table, my other 9 tables will get shifted also.

For example, the first table's range is from A1 to A10, and the second table's range is A12 to A22. I want to add an extra line at the beginning of each table, so as of now, it's A1 and A12. But once I add an extra line on A1, the first table is now expanded to A1 to A11, so the second table's starting point is no longer, A12, instead it would be A13. Therefore, I would not be adding a line at the beginning of each table. I hope that clarifies my problem, Thank you for your time!

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.


I was wondering if anyone may be able to help we with a filtering issue I have.

I have a table of data which has one of it's columns called "Function", which contains multiple values as a comma separated list. On the front page of the workbook I have a multi value list box which contains all possible values that can be found in the "Function" column. I want to be able to allow users to select whichever functions they wish to search for from the listbox, and then have the worksheet rows be filtered to only display rows that contain at least one of their selections within the value of its "Function" column.

So for example the table might look like this:

Row | Name | Function
1 | Joe | enquiry, map
2 | Bob | enquiry
3 | Fred | map, customer
4 | Tom | customer

If the user selected "enquiry" in the list box, i'd want only rows 1 & 2 to be shown. If they selected "enquiry" and also "map" (as it is a multi select list) i'd want rows 1, 2 & 3 to be displayed.

All the list box functionality is working fine, I'm just having trouble working out how to actually filter the results correctly.

Any help much appreciated.


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.


Hello there,

I've seen some answers for "looking up a value and returning multiple corresponding values", but my question is bit more complex.
This is what I have tried However, if I try that with the list I have, I will get the same corresponding values listed multiple times. Instead of listing them multiple times, I would like to count them. Or if that is not possible, at least just reduce it to list the values just once.

I will need to use it in Google Spreadsheet. I attached what I created so far.

to be solved.xlsx

Can anyone please help me?

I have this kind of list:

10_X A A B 20_X B B C 30 A B C 40_X A C C

I'm looking for a formula that returns this:

A 10_X 2 B 10_X 1 C 20_X 1 30 1 20_X 2 30 1 40_X 1 30 1 40_X 2

Any help would be appreciated Thank you

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.