Creating A Dropdown List Using Matching Data From 2 Columns To Populate The List
Jun 11, 2013
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
I am aware there are people with more pressing problems than computer games and as such
We use excel for scheduling purposes and each and every day we fill out a floor chart with who is working that day. As it stands now we have to print out a template and hand write all names or type them in and print it out. What I would like to try and do is define each set of names and allow for easy access to auto fill these names. I feel it needs to be a simple process as not all of our managers experienced in excel or formulas. What I would like to try an accomplish is in sheet 1 cell A1 to be able to create a drop list or formula that that would let me return all the names for a given day.
For the purposes of this discussion we can just define each list as the day that is the header ( IE A2:A4 on sheet 2 would be defined as "monday". Also, I already have the names autofilled based off of what the our schedule has. I'm curious if I could create a drop down list with each day of the week as an option, and if they select Tuesday it will then return all the names defined as Tuesday in a column below.And the next day be able to return Wednesday names and so on.
I'm not stuck on it being a drop down list. Maybe if they could just type and =(defined name) in the box above and it would return all the names.
I have tried an =(defined name) and it does return the value but only for the first cell and it has to be in the same row for it to return the name. Which doesn't work because I would like the names that are being defined on a separate sheet.
I have two worksheet. One worksheet is a form where in the Field Door No. should be a dropdown list. The list will be retrieve from worksheet Source. Now upon selecting a specific Door No. it should also autopopulate the respective Type, Brand, Serial No., and Type.
First thing i need to know is what should i do on Cell B1 worksheet Form for it to be a drop down list retrieving the Column A on worksheet Source.
Second, upon selecting for example ATC0009 on the drop-down, it should auto-populate like these
Door No. ATC0009 Type AT Crane Brand A Serial 43679 Type Crane
I'm trying to make a spreadsheet that can be used to easily build a collective list of steps, for a user to read and follow line-by-line.
I want a source sheet of "steps" that I can change over time, and the resulting tabs that reference the source sheet get updated/populated automatically.
I've pieced together some VBA code from other sources, which kind of does what I want it to:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row > 1 And Target.Column = 1 Then Dim SourceSheet As Worksheet Set SourceSheet = ActiveWorkbook.Sheets("Steps") Dim TargetSheet As Worksheet Set TargetSheet = Target.Worksheet Dim c As Integer Dim Source As Range
But there are some problems:
1) The data auto-populates into a row. It would read better if each step was in the same column, meaning rows would need to be automatically added upon selecting something from the drop-down list. The number of rows that need to be added vary based on the number of steps in the source sheet, for the selection made from the drop-down list.
2) If you make a change to the source sheet, my goal is to have the other sheets referencing the source sheet's lists of steps update automatically, so you only need to update the steps in one spot and everything you've built from them gets updated instantly. Currently, you must select a different choice from the drop-down list, and then change it back, before it populates the "new" steps from the source sheet.
This is my first time using VBA.
What I have so far is attached: testAutoPopulate.xlsm
I have a drop down list with 6 recipes. When I select chocolate for example I want that to automatically populate different cells in the worksheet based on a standard I have created for that recipe. The recipes won't change they just have different ingredients and I want that to show up depending on what I select on the drop down list. I went into VBA and worked a little with this starting code:
If Target.Address = "$F$3" Then Target.Offset(0, 1).Value = "1"
but It wouldn't populate separately based on what I had selected in the drop down it would just populate all 6 recipes the same.
I am looking to auto generate a roster/schedule. I have a spreadsheet with shifts for each weekday that I need to populate every week with names to pick from a drop down list. the drop down list is made using data validation and looks at a dynamic named range to allow me to select only the people who are available to work a certain shift.
I need to create a data validation drop-down that is dependant on what has been selected in another drop-down. However, all the solutions I've come across online so far suggest using the INDIRECT function, which is not appropriate in my case. This is because the data upon which the two drop-downs are based on is itself dynamic. My specific situation is this:
In one sheet, I have a list of university names in one column, and in the next column a list of faculties. The same university can appear multiple times in the sheet (once for each different faculty at the university). This information will be added to regularly, with new universities and new faculties added often.
In another sheet, I want a drop-down in one column that takes its validation from the list of universities. I then want a second drop-down that is only populated with a list of faculties at that university. The INDIRECT solution seems to only work if I can define a static range of cells from which to draw this data. How do I do it in my situation above?
I'm creating a new spreadsheet for different fines we have as a football club.
I have a table frozen at the top which looks like this:
Fine 1 Fine 2 Fine 3 Player 1 Player 2 Player 3
Then also I have a list below this with the date and specific fines:
Date Player Fine Amount (drop down (drop down Entered manually list of players) list of fines)
I am wanting the amount of the fine to be entered into the table at the top of the spreadsheet automatically based on the 2 drop down list entries. I would also need the table to keep adding the fines together once new entries are made.
I am going to use excel for test cases. I can creat a drop down list with the selections "pass", "fail", "not run". That isn't a problem using the data validation toolbar. However I would like to basically color the cell (test case) with a certain color based on the drop down selection.
[URL]....When I select a name in column B, it populates column C's dropdown with that name's list of units. When I select a unity type in column C, I'd like it to automatically populate column E in the same row with the value associated with that unit. The values associated with a particular unit can be found to the right of the sheet. So if in row 2 I select Blake in column B, then Unit 5 in column C, I'd like column E to be populated with 14.
Edit: using Excel 2007.
I attached stripped down version of the sheet which is small enough to post. None of the things I removed should affect the topic at hand. The full sheet is available at rapidshare.
I have a validation list drop down box set up to reference data in a defined name list. I would like to be able to select multiple values from this list to appear in one cell (preferably separated by a comma). For example, the defined name list contains the following data:
Choice 1 Choice 2 Choice 3 Choice 4
I would like to be able to populate one cell with the following via the drop down list: Choice2, Choice 4
I'm trying to set up a billing form for my work. I'm trying to include drop down list for each sections (personnel, equipment, and supplies), then all sum together as a bill. I'm not sure if this is doable in excel.
I typically use the following formula to create a list with no blanks, when I have a single column of data. Is there a way to do the same thing when you have multiple columns of data (side by side to make it simple) and you want to create a master list that gets rid of the blanks, and keeps them in the order they appear (by column)?
For example, if I had data (with some blank cells randomly placed) in columns AM and AN, and I wanted a singe master list in one column of all non-blank cells in column AL, followed by column AM, etc.? Is there a formula to do this, or must I resort to a macro?
I am a newbie to Excel. I really appreciate if someone could help me here and this is very urgent as I have a project going on.
I have a cell say B3, which is a dropdown list I created using Data Validation. It only lists down Yes or No.
What I need is when you click on Yes in B3, i need columns C to I to unhide. If answer is No, column C to I should be hidden. By default, the columns will be hidden.
Can you please help? I tried many times but not successful. Appreciate if someone will be able to walk me through details. If you want to see the sample spreadsheet, I can sent it to you by email. I tried Data> Outline group but they are not happy with this.
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.
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.
I would like to be able to compare the "standards" in Column A with the Assessment status in Column B and the Assessment period in Column C in order to generate a compact list in another workbook (ideally) or tab (if not). A list might typically contain all the standards that have been M (mastered) during a given Assessment period. I realize that filters would achieve this to a large extent but I was hoping to automatically populate another worksheet or tab.