Conditional Formatting Based On Selected Values Of Two Dropdown Menus
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
ADVERTISEMENT
Mar 19, 2014
I want to highlight all the cells in a worksheet that have dropdown lists in blue color. Not sure how to do it.
View 13 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
Nov 26, 2013
Attached is a workbook. I have manually highlighted the cells to give idea of what I would like the outcome to be.
Moonah OPS sheet.
In Row 5(Shift times), under the drop down menu in cells B5:J5, there is a range of Shift times.
And for the purpose of this I have selected
B5 as 0800:1621, manually highlighted B11:B27
C5 as 06:00, manually highlighted C7:C24
D5 as 07:00:1521, manually highlighted B11:B27
E5:I55(RDO, REC, SICK, Travel) can remain as manual input if it is to much to work out.
Is it possible or just a pipe dream, to use some method to automatically highlight a range of cells based on the C5:J5 'time'?
If the time is an actual shift time, the colour will be green and dark green writing as default, then we can allocate jobs/training/meetings and manually colour them.
Daysheet DAY MONTH YEAR template - Highlight.xlsx
View 7 Replies
View Related
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
May 15, 2013
I am looking to write a code to lock/unlock cells based on values selected using a drop down list (see attached Excel) For example if Netherlands is chosen as the Country (Column A), then except for the columns Amsterdam (column B) and Eindhoven (column C), all other columns must be locked. Similarly, if India is chosen as the country from the drop down list, then only the columns New Delhi and Mumbai must be editable for the user, the rest of the columns remain locked.
I tried tweaking some of the lock/unlock codes around, but got lost especially with getting to run the macro - still trying to come to terms with running a "Private Sub Worksheet_SelectionChange" function in the sheet from "Module"!
View 1 Replies
View Related
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
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
Sep 21, 2013
I want to do conditional formatting based on two cell values.
i want to do conditional format the column 'R' based on clolumns 'J' and 'R'.
column 'J' is a text column named PRIORITY which can hold the values P1,P2,P3 an P4.
Column 'R' is nothing but difference of two columns 'M' and 'H'(both are time stamps).Column'R' is in below format.
=TEXT(M4-H4,"h:mm:ss").
column M and H are in below format:
eg: 9/11/13 9:41 AM
Now i want to format the column'R' like
if column J ="P2" and column 'R' > "00:10:00"(10 mins) =it should become red.
if column J="P3" and column 'R'>"00:30:00"(30 mins) = it should become red.
View 7 Replies
View Related
Mar 9, 2009
scenario: Column H needs to be formatted to green if the text in Column F does not equal "Dog". Dog could be either DOG, Dog or dog. I know how to get it done with only dog, but this one is a bit tricky.
View 2 Replies
View Related
Jan 14, 2014
highlighting dates on a premade calendar.
The calendar is made as below:
Tasks
1-1-2014
1-2-2014
1-3-2014
[code]....
The data table is given as below:
Tasks
Date
Task1
1-3-2014
Task2
2-14-2014
I need to highlight the cell in the calendar that matches both the task and the date in the data table. For example- in the situation above, D1 would be highlighted.
View 3 Replies
View Related
Jun 3, 2014
I have a matrix in excel to show % and $ for multiple columns - set up like this:
Rows Part 1 Part 2 Part 3
Criteria 1 % $ % $ % $
Criteria 2
Criteria 3
the $ columns are conditionally formatted to highlight the top 10 values on the matrix. is there a way to have the corresponding % cells also highlighted? The % and $ aren't both top 10, so the % would have to be highlighted based on the $ top 10 cells.
View 6 Replies
View Related
Jul 30, 2014
I have a range of cells where each row relates to an employee.
In Cells (Columns H:L) I want the range of cells on that row to turn blue if the user puts a "H" in one of the cells.
Also it will need to be adapted for "O" , "Y", "X" - each with different colours.
This needs to be repeated on each row for each person.
View 5 Replies
View Related
May 9, 2014
I need to highlight a column in a pivot based on the 3 rules below.
Highlight cell in column A if:
Cell in Column A contains numbers 4, 5, 6. or 6.5.
Cell in Column D contains a value
Cell in Column E contains a value
An example of how it should look (Excel 2010): MrExcelhelp1.xlsx
Formula to put in conditional formatting?
View 4 Replies
View Related
Dec 29, 2013
Here i want to color my cell based on their dates for example : 01-Jan-2014 to 10-Jan-2014.
For the respective date i want to color my bars from G10 to P10.
View 6 Replies
View Related
Feb 19, 2013
I'm creating an excel document that tracks the amount of time someone has (in months) in the program. Certain residents are able to 'fast track' if they meet conditions, and I am trying to create this spreadsheet so that anyone who looks at it can tell who qualifies (and when).
The issue I'm having is with conditional formatting, because I don't know how to do it with mutliple conditions.
Column C is their previous time (months) in the program
Column D is their current time (months) in the program
Column E is their total time in the program (Sum C+D)
Coumn E is what I would like to format, based on the following rules set out in the program manual:
If previous time is 9 months or more, a resident is eligible to fast track after 6 months current time (format green).
If previous time is less than 6 months, a resident is eligible to fast track after 9 months current time (format green).
I understand that their is an odd gap - but these are the rules currently set out by the program manual, which is what I have to follow.
I'm using Excel 2007 - if that changes anything.
View 3 Replies
View Related
Oct 19, 2013
I am trying to apply a conditional format to an entire row based on the presence of any value in the first cell of that row to format to a certain color. If there isn't something present in the first cell, I don't want it to change.
Everything I keep trying ends up highlighting the entire workbook.
I would like to apply the rule to the entire sheet but only have it highlight the rows which have a value/text in the first cell of each row.
I am assuming I should use the formulas selection for conditional formatting but I'm unsure of what formula would apply to this type of request.
View 3 Replies
View Related
Apr 12, 2013
I'm having issues with Excel's 2010 conditional formatting. Seems easy to use, but I'm trying to highlight values based on 2 columns of numerical data. Example:
Column F:
6
6
14
Column L:
3
NA
17
I would like Column L to highlight values that are greater than Column F in green. If they are less than Column L then highlight them in red.
Seems I was able to do this with Excel 2003, but I don't understand the 2010 version.
View 3 Replies
View Related
Feb 4, 2014
I want a functionality in a sheet wherein if i select a value from a drop down, i will get specific fields to be entered in the sheet below and when i select a different value from the drop down the fields to be entered should change.
View 3 Replies
View Related
Jan 5, 2013
I'm running Windows XP with Excel 2010. I would like to be able to have a group of 19 boxes of which each box has a unique entry, ie 1, 3B, 5C etc. Each box I'd like to have a way in which when selected only the single or dual unique characters show as a color. See attached spreadsheet with sample. When a cell with lets say 1 is selected, a drop box appears and the color and description are shown. Select a color but only show the 1 and the color selected, leaving behind the description. How can I do this for all 19 boxes. This seems like its a classic conditional format. Unfortunately it appears to be several orders of difficulty than a normal conditional format.
View 2 Replies
View Related
Jun 14, 2013
I have three drop-down boxes in 3 adjacent columns. Column 1 is free-choice, Columns 2 and 3 drop-downs are variable dependent on what is in Column 1. That works fine.
I have an issue with over-type but I can solve that with protection. Again fine.
BUT:
1. How do I make the user choose something i.e. not just leave the cell in column 2 or 3 blank by ignoring it (i.e. blank is an error but only after drop-down in column 1 is activated)
2. Also, if the user has completed the line (columns 1,2 and 3) and then changes column 1, columns 2 and 3 are now reading from the incorrect drop-down boxes (i.e. they are now in error but this is accepted and not flagged).
View 2 Replies
View Related
Jan 9, 2013
In one column I have a drop down list of a dozen different initials with conditional formatting on those cells. I would also like to conditionally format an adjacent text string column based on those initials, but don't want to set up a dozen rules on each cell, one by one. If I set up the top cell with the correct rules I cannot fill the formatting in because the formula is not updated on a row by row basis, but en bloc for the range.
View 3 Replies
View Related
Jun 10, 2014
I am trying to copy/extract row of data from worksheet(Detail)A:W to worksheet(1st Letter) if X=1st Letter on worksheet(Detail). I have never used VBA and i am not sure if this can be done with a formula.
View 2 Replies
View Related
Jul 12, 2013
I attached a sample spreadsheet but I was wondering if there was a code that could display a range based on an option selected in a dropdown box.
excel help.xlsm
View 3 Replies
View Related
Feb 8, 2012
I have created validated dropdown, which in turn I have used for conditional formatting to highlight specific consecutive rows based on the dropdown.
Is it possible for the worksheet to be positioned based on the dropdown chosen, so that the highlighted rows are visible without having to manually scroll down, looking for the highlighted rows?
If dropdown chosen highlights rows 50-60, I would like the page to automatically show rows 50-60 (not necessarily hide the others, but at least scroll to rows 50-60).
View 1 Replies
View Related
Mar 18, 2014
I am creating a excel calculator to quick and easy calculate CBM (cubic metres) for a total number of cartons and pallets loaded into a container. The value of 1 carton or 1 pallet will be different depending on which type of packaging/pallet it is.
I have created drop down menues in order to choose the correct packaging / pallet. But I am stuck on on to create the formula.
If I for example choose "LANDFROZEN 7KG CARTONS" from the drop down menu, I need the formula to collect the value in cell E18
If I for exampel choose "LANDFROZEN 10 KG CARTONS" from the drop dwon menu, I need the formula to collect the value in cell E25 .... and so on.
I have tried to use several functions, but none is working the way I want them to :-)
View 3 Replies
View Related
Jan 11, 2010
Is it possible to update certain rows only based on the choice selected from a dropdown list
I shall explain this scenario
I have attached a small excel sheet called TESTTHEM which i'm testing
and learning excel simultaneously. On the attached sheet
If i select a value in B4 as BusinessLogic, it should then lookup for relevent rows in MISC worksheet and copy C5:L5 from MISC worksheet and replace
the columns G4:P4
Is it possible to do this in excel?
View 13 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