Index Arrays - Conditions Are Based On Dropdown Menus
Mar 16, 2014
I am looking to create an index array. the conditions for the array are based on drop down menus. I want to have it that if the drop down menu has * in it, the array should calculate everything rather than a specific value.
={IF(ROWS(I$14:I14)>$H$7,"",
IFERROR(INDEX('Sheet1'!$C:$C,SMALL(IF(('Sheet1'!$T:$T=Sheet2!$H$2)*('Sheet1'!$U:$U=Sheet2!$I$2)*('Sheet1'!$L:$L>0),ROW('Sheet1'!$T:$T)),
ROWS(I$14:I14))),"")}
In the SMALL array I have three conditions:
1.'Sheet1'!$T:$T=Sheet2!$H$2
2. 'Sheet1'!$U:$U=Sheet2!$I$2
3. 'Sheet1'!$L:$L>0
The array works fine but only if I place an actual value in the drop down cells which are H2, I2. The third condition is just to ensure it only calculates the array for rows that have numbers above 0. My question is how can I amend it so that selecting "*" in the drop down cells will calculate for everything?
View 3 Replies
ADVERTISEMENT
Jun 25, 2014
Create a dynamic chart which is based on multiple dropdown menus. I'm trying to design a chart which shows figures per city. In the first dropdown menu you can choose a city.
The other two dropdown menus will determine the date range of the chart.
How to create this chart ....
View 2 Replies
View Related
Aug 11, 2014
The worksheet is basically a sales funnel and I want to use a drop down menu selection in ever row of column Z to update the relevant cell, based on the selection, in the same row, column Q to Y, with a date stamp which will then not change as the drop down is progressively changed to suit the relevant stage of the job.
View 2 Replies
View Related
Dec 9, 2011
I am building a spreadsheet with a column having two drop down menus at the top that designate independent, but strict conditions. For simplicity, say I have in cell A1 a drop down menu with choices "alpha, beta, gamma" while A2 has a drop down menu for "1, 2, 3". These six values sit off to the side in cells X1, X2, X3, X4, X5 and X6 respectively.
In cell A3 I woul like to change its color, perhaps to green because it is an acceptable number (say less than or equal to 100) or change to red if unacceptable, value greater than 100.
I can do both of these commands in Excel, no problem. In my situation, I would like to change the value at which cell A3 goes from green to red dependent on the options in A1 & A2. There are 9 such combinations of alpha, beta, gamma, paired with 1,2,3 and I would like the combinations to change the fill color of cell A3 in the following manner:
If A1 = alpha and A2 = 1, then A3 will be green for values less than or equal to 100 and red for values greater than 100.
If A1 = beta and A2 = 1, then A3 will be green for values less than or equal to 80 and red for values greater than 80.
If A1 = gamma and A2 = 1, then A3 will be green for values less than or equal to 60 and red for values greater than 60.
If A1 = alpha and A2 = 2, then A3 will be green for values less than or equal to 95 and red for values greater than 95.
If A1 = beta and A2 = 2, then A3 will be green for values less than or equal to 75 and red for values greater than 75.
If A1 = gamma and A2 = 2, then A3 will be green for values less than or equal to 55 and red for values greater than 55.
If A1 = alpha and A2 = 3, then A3 will be green for values less than or equal to 92 and red for values greater than 92.
If A1 = beta and A2 = 3, then A3 will be green for values less than or equal to 72 and red for values greater than 72.
If A1 = gamma and A2 = 3, then A3 will be green for values less than or equal to 52 and red for values greater than 52.
How to create a cell that can change its conditional formatting based on the selected values of two drop down menus.
I have a few other conditions I would like to implement, say change the fill color of A3 to green, orange or red dependant on the values in A1 & A2, but I might be able to crack that if I can see how the above scenario is done.
View 1 Replies
View Related
Dec 16, 2013
I'm having trouble with doing a similar thing with conditions/criteria based on arrays for different formulas (e.g. IF, COUNTIFS, SUMIFS).
Here's what i'm trying to condition and than use different formulas:
column A - Region
1. Europe
2. Europe
3. Asia
4. Asia
5. America
6. Asia
7. America
8. America
9. Europe
10. America
[Code] ....
QUESTIONS
1. (IF, AND ?)
For all cases listed in column A that have "university" in the column B, If there is at least one case having "Europe" write 1, if there isn't write 0.
The conditions are "Europe" in column A and "university" in column B.
If there are cases like that, I want formula to produce 1.
If there are no cases like that, I want formula to produce 0.
* In this example the formula would produce 1. for "Europe" and "university".
* If the formula was looking for "Oceania" in the column A with "university" in column B it would produce 0.
2. Using COUNTIF with multiple criteria.
Count all of the cases that have "Europe" in column A and "university" in the column B
The conditions are "Europe" in column A and "university" in column B.
If there are cases like that, I want formula to do count.
* In this example the formula would produce 2 for "Europe" and "university".
* If the formula was counting "Oceania" in the column A with "university" in column B it would produce 0.
3. Using COUNTIFS with multiple criteria.
Count all of the cases that have "Europe" in column A, "university" in the column B and "GER" in column C
The conditions are "Europe" in column A, "university" in column B and "GER" in column C.
* In this example the formula would produce 2 for the 3 conditions.
* If the formula was counting "Oceania" with "university" and "GER" it would produce 0.
I've spent a lot of time searching the Internet for this (e.g.how to use IF with an array, about different functions and formulas...) and found nothing that works.
For COUNTIF(S) they just didn't work, event with ctrl+shift+enter.
View 7 Replies
View Related
Dec 10, 2004
I am compiling questionnaires in Excel and need to constrain the answers. I've successfully used validate, list and directed it to a list (containing, for example, 'USB', 'Firewire', 'SCSI'. When I first set this up, the drop down menu appeared (and the prompt message I had created). After I copied the first worksheet to use as the base for my next questionnaire, the drop down menu disappeared, although the prompt still shows and the validation still works.
Have I missed something fundamental?
I've tried creating one on a brand new workbook and copying the worksheet and it seems to function correctly. Why has my drop down menu vanished???
View 9 Replies
View Related
Mar 9, 2013
I have a 'diet diary' that I have built in excel that has many of the common meals I eat and details calories, carbohydrates, fats, etc and I use it to keep track of what I have eaten in the day.
However, one of the features is that on the front sheet I have lots of drop down menus that I select the different things (example, milk, banana, spaghetti bolognese) which in that puts in the nutritional info. However, each day I have different numbers of items to put in so I have to change the slots that no longer have an item in back to the default value so that it doesn't add calories. What would be ideal is if I could highlight a bunch of boxes at once and then make them all select the 'no-item' option in the drop down box simultaneously rather than selecting them all individually.
View 3 Replies
View Related
Jul 18, 2013
What I would like to do is use a drop down list Type 1, Type 2, Type 3 etc and then be able to use whatever is selected to reference a value in another cell.
Excuse my ignorance but something like Cell 1 * (If Type 1 from the list is selected use figure in Cell 3, If Type 2 from the list is selected use figure in Cell 4..........) etc etc
View 2 Replies
View Related
Oct 19, 2012
I need to link this summary sheet projects to this plant and equipment list sheet to show each project numbers separate weekly cost hire on the summary sheet.
View 3 Replies
View Related
Jan 31, 2013
I am trying to add two drop down menus - part number and material description. I would like to be able to choose an item from either menu and have it populate the rest of the row with the item information.
I know how to create the drop down, but I don't know how to make it work so I can choose from either one of the two drop downs and have the other automatically populate.
View 1 Replies
View Related
Mar 11, 2013
I need the drop down menu to update when the user adds a new entry. For example, if the user decides to add companies to the financial table, no matter how many new rows they add, our drop down menu at the top will update to include the new the entries. I want this to be the case in every table. How do I do this?
View 5 Replies
View Related
May 5, 2014
I have put together a user form with two ComboBoxes, one dependent on the other. The first box has a drop down menu from which the user selects a name, and the second drop down populates a list from a named range according to the user's selection in the first box. The code I used is as follows:
[Code] .....
This has worked so far, however, if one selects a name in ComboBox1 and then changes their selection (ex. Initially selects John Smith from the first menu, and then changes their selection to Mike Roberts) there is an error. I am not sure why - I think the issue stems from the 'Clear' function but I am not sure how to resolve it.
View 2 Replies
View Related
Sep 29, 2011
I have a table in cells A1:B11. I have two drop down menus in cells G3 and H3. When I select red in G3 I need Bob, Sam and Sarah appear in the drop down menu in cell H3. Instead, with my current formula, only Sarah appears in the drop down in cell H3. When I select green in G3 I need Tommy, Bob, Sandy, Carl and Phil appear in the drop down menu in Cell H3. Instead with my current formula only Phil appears in the drop down in cell H3.
I linked cell H3 to the defined name Names which equals
OFFSET(INDEX(Sheet4!$B:$B, MATCH(Sheet4!$G$3, Sheet4!$A:$A,0),0),COUNTIF(Sheet4!$A:$A, Sheet4!$G$3)-1,0).
What should I add to this formula to make it work correctly?
ABCDEFGH
1RedBobRed2RedSamGreen3RedSarahBlueRedSarah4GreenTommy5GreenBob
6GreenSandy7GreenCarl8GreenPhil9BlueCarl10BlueSam11BluePhil
View 9 Replies
View Related
Oct 21, 2007
when I sort data columns that contain drop-down menus. My spreadsheet has several columns: "Patient name", "Room #", and "Phone #". Each cell within the "Phone #" column should have a drop-down validation list containing two possible entries: the room's designated telephone number, or NONE. Whenever I add a new patient to a room, these three columns need to be sorted alphabetically by patient name. But when I sort all three lists simultaneously, it looks great- but then I realize that the drop-down lists associated with each "Phone #" cell haven't sorted! They stay in their original location and don't sort with their cell. So the designated telephone numbers for each room are all screwed up.
View 9 Replies
View Related
Apr 1, 2009
i have a table of tools and their properties which i am using to provide validation lists for a number of other worksheets that record when those tools were last calibrated/tested and when they need to be calibrated/tested next.
many tools require more than one form of testing (electrical and/or calibration), but not all tools require both.
can i create some kind of drop-down menu the looks at the tool master list, but only gives the option of those that require the particular type of testing i'm recording on that particular list? how can i create a drop-down list that is more limited than the range i have selected for it to display?
View 10 Replies
View Related
Jun 11, 2013
I have a problem (I have intermediate knowledge of Excel): lets say that I have two model years for Cadillac: 2013 and 2014, and have 3 car models for year 2013 (ATS, XTS, and CRX) and two car models for year 2014 (ELR and CTS Sedan). I want to create a dropdown menu under "Model Year" that will show me 2013 and 2014. When I click on either one, a dropdown list for all car models for that year can appear in the next column under "Models". Then, for each car model, there are different characteristics that are in other columns, such as horsepower, mileage, etc...I want all of the content to stay on one line (row), but change when I click on a different model.
View 2 Replies
View Related
Nov 5, 2013
On the spreadsheet there is 31 days (a separate worksheet per day).
at the bottom of each sheet there is the case notes for that day. I would like all that information collating on the last sheet of the workbook (named Cases).
I would like to be able to sort through that data with the use of some drop down menus.
All i need to sort is the Brand and if the case is open/closed.
what to do so I can try myself or using the attached spreadsheet be able to sort this problem out. If you choose to use the spreadsheet to meet the requirements
View 14 Replies
View Related
Jan 11, 2014
I am trying to build a complex equation that has multiple parameters filled by several choices from several different dropdown menus.
For instance:
I'm building a set of 5 dropdown menus, that each lead to filling certain boxes with a number created from a formula. I've provided a small sample to explain what I'm trying to do.
My problem is, I'm unsure of how to make it so certain drop down menus affect the information inside of the next. Like, I want to have someone select "Oscar" under the characters, and have the "Lances" list come up in the Weapons column menu. I don't want the Weapons menu to be selectable in any way unless a "Character" has been selected.
View 4 Replies
View Related
Jun 26, 2014
I work for a Machine Shop in the Toledo area. We use a quotation sheet to quote our products to our customers. It has 3 cells that describe the Customer, the Contact person at that customer and finally that Contact's Email Address. I have a drop-down menu in each of these cells. The first, Customer, is based off a simple list of our customers. The Contact drop-down then uses the Indirect Function to search the worksheet for that Customer. The drop down is actually based off of a Range Name saved as that Customer's Name.
However the problem is many of my customers have & or , and since Excel does not allow those symbols or even spaces in a Range Name I have to use Substitute to be able to keep the spaces and the & or ,.
It is important that on the Quote Sheet the Name of the Customer is identical to the actual name of the company so I cant use AND instead of &.
Currently my Second drop down, Contacts, has a formula that looks like this {=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($H5,"_"," "), "3", "&"), "2",","))}.
My problem is that when I give the Range Name a Name , my drop down doesn't work and Excel tells me that the above formula results in an error.
View 6 Replies
View Related
Mar 17, 2014
I have attached a sample from aspeadsheet I am working on. The option reply sheet has '1' entered if the student has opted to take this course. On the other subject sheets I would like the names who have opted for that subject to appear. Stuidents can opted for multiple subjects. I have tried Match and Index functions but it is difficult as students can opt for more than one.
View 1 Replies
View Related
Mar 26, 2009
I have 2 sheets recording feedback and summarising the overall percentages for each trainer.
My boss wants me to do this by month!?!?!
I have created a third sheet but I am not sure how to do the following:
Look up the trainer name
Look up the month
identify the percentages for each category
create an overall average of these percentages
So for example if Jon smith trained twice in JAN getting 100% and 50% in cat1, it would display 75% in the cat 1 cell and so on.
View 7 Replies
View Related
Jun 9, 2008
I am using the following code to create a custom command menu.
Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
On Error Goto 0
Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")..........................
I want to add a new button control at the bottom of the menu underneath the holiday control but it keeps adding it on the sub menu, which leads off the holiday button control. I want the menu to look like this.
Open Net 2 Access
Add Employee
Edit Employee
Delete Employee
Holidays (3 options on sub menu)
*New Control
View 4 Replies
View Related
Feb 3, 2010
From this link: [url]
there is some clever code to create a customized menu in a dynamic Add-Ins section of the Excel ribbon; this section disappears when the workbook is deactivated and re-appears when it is activated. The custom menu has 2 main items each of which can launch a macro. There is also a 3rd main item called "Next Menu" which cascades nicely to a sub-menu. That sub-menu can have several items of its own. Very cool code.
However, I am trying to add a 4th item in the main menu, say, "Next Menu2" which also cascades to a sub-menu. The problem I'm having is when I try to replicate this code to accomplish this, "Next Menu2" appears in the sub-menu of "Next Menu", rather than as a separate (4th) main item below "Next Menu".
I believe there was another post (not sure of the date, but has since expired) which asked basically the same question as mine. There was a reply with code saying "This should work" but when I tried it, it didn't help.
View 3 Replies
View Related
Oct 4, 2007
I want to create a submenu to an already existing user defined menu.
After creating and saving the sub menu using Tools ->Customize ,it works fine. But when
I reopen the Excel sheet it is not found.
I could not find any macro related to the original User menu.Also this menu gets displayed after the AUTO OPen macro in my excel has worked. This menu gets displayed for only that workbook and not for others.
I have been trying to find out a way to create Sub menu for that ,but could not?
View 3 Replies
View Related
Jan 12, 2007
Need to kown if Index and Match can be used if you have to criterias. Below is a glimse of my sheet. The data does come from 2 sheets, I don't know if that is another reason, doubt it, but needed to tell you just incase. I have sales information and I just want a quick view of the sales by associate by date. My 2 condition are the associates id# and the date.
=INDEX(($J$2:$Q$4),MATCH(AND($A2,C$1),AND(N2:N4,M2:M4),0),9)
my range is from J2:q4, my criteria is column a2 Sales asso Id and C1 the date, and match is N2:N4 & M2:M4, witch is the 9th column.
View 9 Replies
View Related
Sep 28, 2009
I have a very large database, filled with every job we're working on and all the details about it - duties, employees, vehicles, material-costs, etc.
I am currently working on a 1-page "Summary Sheet", that would be used to wade through all of this info and only give me the information I am looking for on one job. I would like to be able to provide drop-down menus on this "Summary Sheet" so that management can pick the information they'd like to see, and not be bombarded by everything else.
Ideally, what I am trying to do is to have a space on top where somebody could enter the name of the job, and then when they pick an item from the drop-down list, it would pull that info from the database via an "Index/Match". When somebody enters a job-name and picks "Joints Tested" from the drop-down, it will only display how many joints were tested on that job.
I already have the drop-downs created, and have other Index/Match formulas in other spreadsheets (they trip me up a bit, but I can generally fiddle with them until they work correctly), but can't figure out how to make it work this way. My database has headings above each column, and I'm writing those same headings next the different options in the drop-down list, but I can't figure it out.
View 6 Replies
View Related
Dec 20, 2009
I have two drop down menus: Class Name and Class Period.
Class name returns reading, math, science, or social studies.
Class period returns 1,2,3, 4,5
I need a formula for a cell that will return the name of the first student in whatever criteria is selected by the two drop down menus.
Example: If reading 2 were selected, I would want the name of the first student in reading period 2.
I did start out with nested If's combined with ands, but that is too many nested ifs. I figured there was a better or easier way to do this.
Formula I thought of: =IF(AND(G3="Reading", H3=1), '[Reading 8.xls]Sheet1'!$A6, IF(AND(G3="Reading", H3=2), '[Reading 8.xls]Sheet1'!$D6)) etc.....
But if I use that there would have to be 20 nested ifs, which I think I read someone nested ifs cap at 9.
View 8 Replies
View Related
Dec 29, 2009
hope the below example is clear to understand and makes sense .....
View 10 Replies
View Related
May 8, 2013
I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
.
. extending until the end of the data set
.
I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:
Code:
Public Sub CSVparser(file As String)
Dim Lines As Variant
Dim j As Integer
Lines = Split(file, vbLf)
ReDim breadCrumbs(UBound(Lines)) As Variant
For i = 1 to UBound(Lines) - 1
breadCrumbs(i) = Split(Lines(i), ",")
Next i
End Sub
View 1 Replies
View Related
Mar 2, 2014
Populate my drop down list. I have 2 columns. I want to populate my dropdown list with data from column a that has a specific value in column b. is this possible with out using VBA?
E.g.
Column A / Column B
123 / TEST
126 / TEST
100 / BACON
231 / CHEESE
basically I would like a drop down list that contains the constants from column b. so from the example above it should contain 123 and 126 as options.
View 6 Replies
View Related
