Return Corresponding Value When Drop-Down List Changed
Oct 3, 2009
I'm making a spreadsheet where I want the user to select an item in A1 from a List made using Data Validation. When the person makes the selection in A1 I want the next 5 columns to automatically display specific attributes that belong to A1. However, I'd like the user to then be able to change each of these items (in B1-F1) from their own individual Validation Lists. I'm using this to mix and match mythical monsters. So in A1 I have well known monsters like Jaws, Godzilla and the Lochness Monster. When you select one of these creatures, that creature's specific attributes show up in the next 5 columns -- things like Appearance, Motive, MO, Hideout, and Special Features. I then want to be able to change Appearance, Motive, MO, Hideout, and Special Features individually from their own Validation List. I basically just want to mix and match between the various creatures.
I've gotten it only partially working by doing the following: At the bottom of the page (for simplicity, I have everything in 1 sheet but I intend to move the "data" table for the Lists to their own sheet) I have a table where in the row for each creature I have the different attributes. A1 (my "creature name") was made using Data Validation from the A column. For the next column I made a Data Validation List for the items. Once that pulldown was made, I then went to the B1 cell and used VLOOKUP to autofill the cell when the person makes their "A1" selection. I repeated this for cells C1-F1. This works ok the first time you make a selection. But whenever you change the "default" value for B2 using the validation drop down list, the Vlookup formula is replaced by the actual text value. This results in the cell not changing when the person next changes the A1 value.
View 3 Replies
ADVERTISEMENT
Jul 23, 2007
I am using INDIRECT to create a second dynamic drop down list (L1) that is based on the cell contents of a first drop down list (I1). I want to create a macro that will blank the 2nd drop down list ONLY when the choice in the first list is CHANGED. I have the following, but it blanks the second list as soon as the first list is clicked on, rather than when a change is made:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Range("I1"), Target) Is Nothing Then Range("L1").ClearContents
End Sub
View 2 Replies
View Related
Jun 20, 2007
I am trying to create an if function that returns a drop down list (which i have already created in another cell) to the cell if a certain condition is met. When i try, the if function only returns the option that has been selected in the drop down lists cell, instead of the entire drop down list. Is there a way to get the If function to return a working drop down box?
View 3 Replies
View Related
Jun 3, 2014
I have created a drop down list of various material sizes what I am needing to do is to select a material size from the drop down list and a corresponding value is input into the cell. So on say sheet two (Data Sheet) I have Cell A1 25x25 with Cell B1 100, Cell A2 30x30 with Cell B2 120 and so on. On sheet 1 Cell A1 has my drop down list being the material size ie 25x25, so what I need to happen is if I select 25x25 then Cell B1 is 100 or if I select 30x30 then cell B1 is 120.
View 4 Replies
View Related
Nov 16, 2007
I am trying to create a quotation spreadsheet and want the drop down list to contain the materials but once a selection is made in the drop down list, i want other cells to be accordingly populated with data stored on another worksheet within the workbook.
The source of the drop down list is material and i need other cells to be populated with the adjacent data.
Here is a small list i have at present, which will be much larger when completed.
MATERIAL 20mm 30mm
Absolute Black £62.15£83.30
Absolute Black Premium£71.65£96.10
African Red £58.20£78.00
So if the selection in the drop down list is African Red then it should accordingly populate other cells with the values £58.20 and £78.00. Please help as i have tried many sources elsewhere but without any luck and this would be very benficial.
View 7 Replies
View Related
Jul 2, 2014
I need VBA code to do the following:
When cell H2 = "Weekly", I need cell I2 to return "50".
When cell H2 = "Bi-weekly", I need cell I2 to return "25".
The value in Column I should change as soon as the value in Column H changes.
NOTE: The value in Column H is chosen from a Data Validation List.
I'm assuming offset is the best option?
View 7 Replies
View Related
Mar 18, 2014
I am trying to create individualized worksheets based off a master worksheet. I have different committee names that I want to appear on the individualized sheets, based on whether the person is apart of that committee or not. On my master sheet, I choose the persons name under the committee and I want that committee name to show on the individualized sheet. For example, I want A1 on Master to show on Name 1, B3 and A10 on Master to show on Name 1, C3. Can I write a formula to achieve this or or am I asking too much? In my actual file, I will have about 10 names and 5 committees with one person being on up to all 5 of the committees.
View 1 Replies
View Related
Mar 19, 2009
I'm trying to create a drop down list which returns values based on what has been selected in the previous drop down list in the adjacent cell, e.g. if 'Apples' is selected in the previous cell then you should only be able to select from 'Gala, Granny Smith', or if 'Oranges' is selected you should only be able to select 'Seville, Blood Orange'. Is there a formula which would do this, or can I use a pivot table somehow? I'm totally stumped.
View 2 Replies
View Related
Oct 26, 2009
I need to have a drop down list which displays a different set of values depending upon the value selected by a previous drop down list. ie. (drop down box 1)= x, y, z. (drop down box 2)= either x1, x2, x3, or y1, y2, y3, or z1, z2, z3. I can produce a single drop down box thats not a problem but linking several drop down boxes is beyond me .
View 4 Replies
View Related
Aug 25, 2008
I have found a macro that should work with a change of a specific cell.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$7" Then
Call Button
End If
End Sub
However, the code doesn t work as with a change of A7 nothing happens.
A7 equals to result from a drop down list --> A7=A5(which is a drop down list value). The "Button" macro is a combination of 4 macros that get rid of zero values and names in pie charts (maybe this plays any role). The master macro is assigned to an object. The code is:
Sub Button1()
Sheets("report").Select
ActiveSheet.ChartObjects("Chart 140").Activate
ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
False, HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:= _
True, ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False, _
Separator:=" "...................
View 8 Replies
View Related
Dec 9, 2009
I have a drop down box in column N, however I would like the options in that drop down box to change depending on what appears in column M.
If column M Says "Lapse" then I want one drop down box to appear in column N, If coumn M says "NTU" then I would like column N to show different drop down options.
View 12 Replies
View Related
Jan 17, 2014
I have in the attached file four variables, with their values in B3:B6, and the results of applying a formula to them in B8.
How to determine which value variable C must be changed to so that the formula returns the result in B10?
Solution.xlsx‎
View 4 Replies
View Related
May 4, 2008
The format for the sheet is set in stone and can't change, and therefore my macro's operate on set columns / rows amd cells.
Because the sheet is huge, im trying to figure out a way to only run the macro's that need to run and more specfically on the changed location.
The Question
I need to create a routine that on the user changing a cell, it returns the cell position (row,column), then it will call a routine i have already which decides based on the cell location, which macro to run and where.
View 9 Replies
View Related
Mar 11, 2014
I have a cell that has a validation list. When i select a value from the list, i want the value of another cell to change automatically but it isnt working. The list source is pointing to another sheet:
=INDIRECT("DB_DAT!$J$268:$J$275")
I get an error when it tries to change the cell value. Its error no. 1004.
View 3 Replies
View Related
May 13, 2013
I used the "record macro" to record a simple sorting (high to low) of columns A-F based on values (0-3) in column A. Everything works when I initially institute the macro. However, when I change anything or add a new item at the end of the list, the order reverses and sorting goes from lowest to highest.?
Here is what ended up in the macro:
Code:
Sub SortColumnA()
' SortColumnA Macro
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
[Code]....
I noticed that it only includes a fixed value for the length of the list (row 90). Is there a way to have it increase when anything new is added?
As of now I plan on just making an arbitrary large index (~200 ish) to automatically grab anything new.
View 1 Replies
View Related
Jan 13, 2009
I am trying to create a fairly simple spreadsheet with about 8 columns and about 400 rows. One of the columns features a drop-down list with about 8 or 9 different options. Dependant on which option is selected, i would like the entire row to change colour with that option.
For example:
FAILED - whole row changes red
SUCCESSFUL - row has no fill
Tested - row changes to orange
etc.
Is this possible within Excel 2003?
View 9 Replies
View Related
Jan 7, 2009
I am trying to generate a list based on the value of a cell. That list will then be used as the range for a drop-down list. Example: Cell A1 returns a value of 15
A drop-down list displays the values 1 through 15. Cell A1 changes to 20
Drop-down list displays the values 1 through 20. I assume I'll need a two-step macro to accomplish this but I can't figure out the logic to populate the drop-down.
View 3 Replies
View Related
Apr 12, 2013
I have a workbook that has two worksheets. One worksheet is an input worksheet. A user will select a date from a drop-down list and type in the events that occurred on that date in 8 adjacent cells. The user selects a button that advances date and clears form. On sheet 2, whatever was typed into sheet one is saved via VLOOKUP formula. However, if I try to change something retroactively and select a previous date from the drop-down list, it clears everything in sheet 2 that was typed for any selected day.
View 1 Replies
View Related
Aug 9, 2014
provide some code which lists all today changed Excel files on the C drive and hyperlink them.
View 3 Replies
View Related
Sep 3, 2009
why my drop down list defaults to the middle of the list when first clicking on the arrow that opens the list? I created an alphabetical list of all US airports but when I open the list, it starts at "D" instead of "A" which is the top of the list.
View 5 Replies
View Related
Dec 13, 2006
I need to know how to create a drop down menu from a very large spreadsheet of Column A but have it only return 1 occurrance. NOT in VBA.
Example:
Column A - these are different Value Streams PMV, SMV and WRV but this will show up over 100 times each as there are many departments per Value Stream I would only like to see 1 occurrance
Column B - Is a list of departments per value stream but will have duplicate departments Example: PMV - PM9, PMV - PMA, PMV - PMB
I would like to have a drop down and be able to select from the three value streams (Column A) and it return one occurrance then the departments that correspond with the Value Stream (Column B).
View 9 Replies
View Related
Jul 18, 2009
I have zero understanding of how to do VB. I am trying to create a risk assessment template where I have a ton of questions to which I want a column E impact drop-down selection of N/A, High, Medium, Low; a column F probability rating of the same. This SHOULD a result in the subsequent two columns result based on those selections.
For example, Column E selected as High and Column F selected as High should make Column G automatically display High and, ultimately, column H to display 100.
Here is where I am at for trying to get Column G's result though all I seem to be getting is a FALSE response to each state instead of the intended result.
=CONCATENATE(IF(E11="High"&F11="High","High"),IF(E11="High"&F11="Medium","Medium"),IF(E11="High"&F11="Low","Low"),IF(E11="Medium"&F11="High","Medium"),IF(E11="Medium"&F11="Medium","Medium"),IF(E11="Medium"&F11="Low","Low"),IF(E11="Low"&F11="High","Low"),IF(E11="Low"&F11="Medium","Low"),IF(E11="Low"&F11="Low","Low"),IF(E11="N/A"&F11="High","N/A"),IF(E11="N/A"&F11="Medium","N/A"),IF(E11="N/A"&F11="Low","N/A"),IF(E11="High"&F11="N/A","N/A"),IF(E11="Medium"&F11="N/A","N/A"),IF(E11="Low"&F11="N/A","N/A"),IF(E11="N/A"&F11="N/A","N/A"))
View 3 Replies
View Related
Jun 9, 2008
I want to get the value for the selected item from the combo box in excel. I don't know very much excel but I want do this forlmular.
See attachment. I want in this box real value…. If I choose livingroom I want to show value of livingroom = 20
View 2 Replies
View Related
Feb 12, 2008
My Main data in my workbook is copied from another workbook. From there it is sorted into separate worksheets for each customer via a macro. I want to be able to set something up so that I can list the customers and then list job descriptions, products and pricing from the correct sheet and return the selected data back to its original form in sheet1.
I am also trying to run the macro that sorts the data in this workbook from another workbook.
I have attached the file so you can see what i am trying to explain.
View 9 Replies
View Related
Jan 26, 2009
I need to create a 'sub' list from a drop down list.
I have 8 categories in a drop down list in column A, but i need column B to also have a drop down list of sub categories depending on what category is selected in column A.
And once column B is populated, i need column C to auto populate with a value. All data is stored on the spreadsheet.
View 9 Replies
View Related
Jul 17, 2009
I have a worksheet containing a list of states in column A and a list of companies in column B (along additional data in columns C-L). I want create a lookup formula on a separate worksheet where users can select the state from a dropdown menu and it will return the information from columns B-L for that state.
For example:
A B C D
Alabama ABC Company Active Expires December 2009
Alabama 123 Company Expired Expired April 2008
Alabama XYZ Company Active Expires August 2009
Alabama Larry Company Expired Expired May 2006
How can I do this without losing my mind? I've tried various Index, Small and Match formulas and none of them work.
View 9 Replies
View Related
May 22, 2008
i have a spreadsheet with several fields. i want to derive information from this data. i want to be able to control the output through the use of list/drop down boxes.
so the user will choose several criteria AND based on this criteria, the ouutput will reflect user requirements.
View 12 Replies
View Related
Dec 1, 2007
I have a workbook with around 35+ sheets on it and I was wondering if its possible if I could put these sheets in a drop down list in say cell A1 so it would be easier to navigate the sheets. Is this possible with out using VBA code if so how would I go about doing it?
View 9 Replies
View Related
Jan 13, 2010
I have a drop down list of shipping Companies. If the name of the company is not in the drop down list how do I allow the user to type in the name then have it save in the drop down list source?
View 9 Replies
View Related
Nov 1, 2008
I currently have a drop down list in sheet 1 and the list is being refrenced from sheet 2
Lets say the drop down list is for fruits and my current list in sheet 2 has 4 items Apple, Grape, Mango, Peach.
Now this is what I need :-
1 ) lets say a user wants to enter Orange (which is not in the list ) he can still do so by entering it manually into the cell.
2) Once Orange is entered which is not in the LIST in sheet 2 then that new name gets added to the LIST in sheet 2 so that next time that name appears in the drop down list automatically. eg Apple, Grape, Mango, Peach, Orange [ but Items should not get duplicated eg Apple, Grape, Apple, Mango, Peach, Apple
View 6 Replies
View Related