Is there a way to create a drop down list from a comma delimited list in a single cell? For example, col A is Name & Col B is the delimited list - Blue,Red,Green (list can be different for each name). Would like a drop down list in col C that allows you to pick one of the values from Col B.
View 3 Repliesexcel spreadsheet that has data in column A like in the attached example spreadsheet. How can I make this into a comma delimited list? So something like this...
food
drinks
home
shelter
ocean
water
to this ---> food, drinks, home, shelter, ocean, water
I have a list of terms in a spreadsheet. Assume they start in cell A1 and they descend down for the next 300 cells. Basically I need to pull those terms into a single text string where the terms are comma delimited.
What I have been doing is concatenating them so they all end with a comma, copy them 25 at a time, pasting values and transposing then running a concatenate formula for the 25 terms. Do this 10-15 times to create that many comma delimited lists then concatenate those lists to create one all in one list.
Example:
The list (starting in A1) looks like this:
Dog
Cat
House
Car
Boat
Mom
Dad
but I need:
Dog,Cat,House,Car,Boat,Mom,Dad
If possible to do this with a formula please do so as my knowledge of using VBA modules is limited but if this must be done using VBA please realize that I'm in the thrid grade compared to your knowledge so please explain how to implement the module as clearly as possible.
How to have a validation list (drop-down) from a single-cell in which values are separated by a comma.
Example I have.
I have a wine list which have many categories of which
COL A / COL B / COL C
Wine Name / Wine Producer / Vintage
I wrote a VBA code to look-up duplicates names and give the producers that make them. The names of the producers will be in an invis cell as Comma Separated Values.
For example: Wine 11 is made by producer X and Wine 11 is made by producer Y. I will have X,Y in a cell which need to be in a drop-down menu when Wine 11 is selected.
I got everything done. The only issue is to get the list. The problem is that the drop-down list shows X,Y as a single component and not as 2 drop-down components. If i were to write down X,Y in the validation list tab, it will show it as 2 components but referring to the cell doesn't.
I need to create a comma delimited list based on variable start and end values for each row.
StartEndOutput List
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200420072004, 2005, 2006, 2007
200420082004, 2005, 2006, 2007, 2008
200520082005, 2006, 2007, 2008
200620082006, 2007, 2008
200820082008
I'm not a VBA expert, or I would have created a Do While or For Each loop.
I have a cell in my spreadsheet that I use to control the size of a column. I formatted the cell as a number and added data validation to make sure the number is greater than zero. Then I realized that I need to also allow for the option of auto sizing the column as well. This would require a data validation list type with something like True and False for the choices.
What I want to be able to do is have it both ways. I want a drop down list in that cell with the options Auto Size or Custom. If Auto Size is selected the cell value gets set to that. If Custom is selected I then want to be able to type in a number.
ddl.png
[URL]
Is there a way to setup a cell such that the value is either an item from the drop down list or, entered directly (depending on the list selection)?
I know I can do it with dependent lists but that would require two cells, one for the auto size mode (true/false) and the other for manual width value.
I have 3 related dropdown lists that work perfectly, if you enter data from left to right. But, if you click in the cell containing the second list, which is based on the first list, of which nothing has been selected, you can just type any value in the cell. Is there a way to prevent this, or at least validate that what is entered is a value in the list.
View 5 Replies View RelatedIs there a SUM formula I can place in cell B1 that will sum values I have in A1 expressed as 1,4,6,7. The number I should see in B1 is 18.
View 14 Replies View Related{= SUM(IF(({325,481,342,440,425}=ID)*($A37=DateRng)*1, ROUND(Sales,2),0))}
I am currently using this formula to retrieve total sales by day for each team and it works perfectly. Data is stored in columns by Date,Salesman ID,Sales.
I would like to replace the array portion with a vlookup to return the array set so i can use drop-down to select different teams and see the sales for that team.
{=SUM(IF((vlookup(TmName,Teams,2,0)=ID)*($A37=DateRng)*1,ROUND(Sales,2),0))}
This is the function as I thought it would work, but the vlookup returns "325,481,342,440,425" as a string not an array.
fill the column "Level 2 Area" based on the value selected from "Level 1 Area" which is coming from a drop down list. So the "Level 2 Area" will be a drop down list also based on the selected value from the drop down list from "Level 1 Area".
Level 1 Area
Level 2 Area
"Is there a way to make values in a cell that are comma delimited (ex. cat, dog, fish) became separate labels in a pivot table instead of getting labels like 'cat, dog', 'fish, cat', 'cat, dog, fish' which represents the exact value in the cells.
The only real option I can think of would be to make an extra rows with the same data for each pet type. For example if the pets were 'cat, dog', there would be two almost identical rows except one would have 'cat' and the other 'dog'."
I have made a vba setup which can solve the problem. But it is pretty complicated and unstable. I need a solution which is not vba based. ~Or a least a very simple code!
I'm creating a spreadsheet to keep track of my costs of production in an online game. Within the game there are a range of spawned resources that appear for only a short time before being unobtainable these resources have specific types that is shared between multiple spawns of the resource but each resource spawn has a unique name.
My first worksheet lists all the resources and their various qualities and the later worksheets are meant to allow me to choose from a list resources matching the requirements of the item I'm looking to craft. The example i have shown in the second picture requires Tatooinian Fiberplast and Lokian Wild Wheat to craft so in the Chosen Resource column I would like to have a drop down list allowing me to select the named resource type i would like to use - for Tatooinian Fiberplast the only thing on the list should be Omnitwixi and for the Wild Wheat it should show Fizi and Krad
[URL]....
[URL]....
I am aware there are people with more pressing problems than computer games and as such
I found code online that I can put on my sheet to get my formatting properties to stay the same for the items in my dropdown list located on another page. However the code does not work for conditional formatted cells...which is what I need. This is the code that I have that will carry over regular formatted cells. Just not Conditional formatted cells.
View 1 Replies View RelatedI'm looking for a way to get a unique list from a column to a data validation drop down list. Any fancy formula or vba script to create a UDF which. Does this?
View 5 Replies View RelatedI Attached a sheet for what i'm asking about ,, i sent it before but the sheet showing it more clearly
View 10 Replies View Relatedhow to list values from multiple columns in a dropdown list based on lookup value of 1st column as below.
This is how the table looks like.
Product MOLD1 MOLD2 MOLD3
4" AB1 AB2 AB3
6" ZA2 zd4 -
This is how the dropdown list should look like for Product 4"
ab1
ab2
ab3
Im trying to organize a tools inventory list. Its supposed to be sort of like an order sheet for each department. But what I want to do is when I select either CARPENTRY, or any of the other trades from a drop-down list, I'll be able to select from another drop-down list items associated with that trade in particular. So eventually I want the master data hidden, preferably in another sheet. I searched all of Google but it seems a bit complicated to get it done. I've attached the sheet so that you can see what I'm talking about. MOVE IN MASTER LIST.xlsx
View 8 Replies View RelatedEasy way to find the drop down list , if more than 500 list. at least the very first letter of the text.
View 1 Replies View RelatedHow do I add a drop down list to a cell?
View 3 Replies View RelatedI inherited the attached spreadsheet and cannot figure out how to change the dropdown control and the tool tips? If you click in either of the top two yellow cells a form control dropdown appears...I can't seem to get to the properties of the dropdown? Also, when clicking in each yellow cell...a tool tip like pop-up appears. Those I cannot find a way to edit either? I can't find any code for any of this...and they all act the same when I disable macros?
View 2 Replies View RelatedWhat is the formula in excel where the cell in reference return a value from a drop down list?
For example: I have a drop down list of HomeLoan Programs with rates from 3 different channels. If I choose a 30yr FNMA with rates 3.875 = 100.125 ; 4.00 101.250; 4.125 = 102.00
If I choose 20yr FNMA it will return the pricing to the cell next to the program.
I provide a spreadsheet.
30yr FNMA works from the drop down but all other programs does not work. I am not sure if I have to reference and index or match.
I'm trying to create a drop down list that will define the name of the cell so I can reference it in formulas or codings to change the format of various cells defined by a specific name.
I'm not even sure if I can have multiple cells defined as the same name
I'll be inputting data into cells, and I want the color of the cell to change depending if the data is one of these criteria: 'In Service', 'In Transit', or 'Must Purchase'. I can't just make separate criteria columns to list all the data, because the data is already placed in a specific order. So I want whoever is viewing this to know which entry fits which criteria from the color of the cell.
The only thing I could think of is inputting the cell data value, and a drop down menu to define the cell name. I'll input a conditional formatting function to format based on the defined name criteria.
I was wondering if there is a way to have a single cell display a value and a drop down list that shows the chosen value. For example, I want to be able to enter into a single cell "365" and select "Hours" from a drop down list and have both be displayed in the same cell. I want to do this because I like to be able to sort my spreadsheet and I have a lot of information that spans across the row that I want to keep tied together. Is this even possible?
View 2 Replies View RelatedI would like to use or not use a dropdown list in data validation depending on the value of another cell. The application is to be able to choose a state from the dropdown list only if the country selected in another cell is "US". If the country is not US then I want the user to be able to fill in free text and there be no dropdown list. I know it's possible to have a dropdown list and allow entries without it being on the list but I need the dropdown list not to appear at all if the value is not US.
View 7 Replies View RelatedI am creating a spreadsheet questionnaire and I have a cell where a selection is made and based on that selection, I want it to populate another cell with a different list of information. In simple form:
Drop down picks a name: Bob
Quote number (when bob is selected): 34xxx
I am trying to create a drop down list that shows the description of different codes. i.e.
1000 - Straight time
1010 - Vacation Time
1020 - Sick Time; etc.
In the spreadsheet, I only want the number to show (1000, 1010, or 1020).
Is there a way using a macro to determine which cells have a dropdown list and then retrieve the name?
View 4 Replies View RelatedIs it possible to have an in-cell drop down list to select a value from, while still being able to enter free text?
View 2 Replies View RelatedI want to produce a drop down list of values and when you click on the different options it takes you to a cell corresponding to the option selected.
View 9 Replies View RelatedFirst, I have read through the board, and I was not able to find a solution already posted. I apologize if in fact this is a duplicate.
Second, I am comfortable with dynamic lists, dynamic ranges, chained validation lists, etc: so I am pretty sure my question is not a repeat of the other recent validation list questions.
Therefore:
Is it possible to generate a validation list from a single cell with a comma separated list?
For example, if I have “Fresh, Cool, Hot, Neutral, Smooth” in given cell, can I then have those 5 items appear as separate choices in a validation list?
I have tried every combination I could think of using arrays, named lists, the INDIRECT function, etc. I cannot seem to get it to recognize them as separate items. For the moment, I have created a bad (too long/complex) workaround where formulas are used to pull out each of the 5 strings into individual cells.