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
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.
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 RelatedI 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.
I have a drop list for selection of staff in an area, but can i then do another droplist and use the sumif function to sum up the total time depending on the value of the list.
i.e., if i selected name 1 it would search all of the sheet to see if name 1 was there and then add the times.
See attached : Utilisation.xls‎
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?
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 RelatedI have a workbook with the twelve months of the year as seperat worksheets. a thirteenth worksheet is a "total" page. I have three different names to choose from a drop down list in collumn E. I need a formula that will say something like, "if JanuaryE4 = John Doe, total page A1= January A1" Is this possible?
View 5 Replies View RelatedI'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 RelatedI 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 RelatedI am looking for a formula which will return the names of in Column A where Column B is equal to 0.
ColumnA
David ColumnB
0 Joe 0 Susan 1 Steve 2 Janet 0 Bonnie 8 Emma 8
What I am trying to do is have a drop down list in one cell and have the next cell bring in the correct price for the item in the first cell. Sounds fairly easy? Well both lists are 200 items.
I have been trying to do this with just three items so far. On sheet two, I have created two lists. The first list in column A contains dogs, cats, mice. The second list in column B contains 1, 2, 3. On sheet one, I have created the drop down list for the animals in the A12 cell. I would like for B12 to auto populate the corresponding number to the animal without having to create an if formula for each animal and number.
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.
I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:
Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula
The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:
{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}
This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.
In "Money" I have:
IE; columns B, C respectively have cust #A100 & 20.00
columns E, F respectively have cust #B100 & 40.00
columns G,H respectively have cust #C100 & 60.00
Etc.
(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)
So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.
I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
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?
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.
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 RelatedI 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).
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"))
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
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.
Function Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?
Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double
[Code]....
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.
I've got an IF statement that's based around what's chosen from a drop-down list. The problem is I'm using "" to stand for nothing being chosen in the cell in question, and this is returning an error message and highlighting the '""' (that looks confusing).
I've tried checking and unchecking 'ignore blanks' in data validation but nothing much has happened.
Here's an example of the formula I'm using:
=IF((AND(C40=A,D40="")),C9,(IF((OR(C40=A,D40=DA)),C9/2,"")))
The problematic "" is the first one.
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.
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.
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?
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 RelatedI 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
