Conditional Range Formula
Apr 1, 2009
Using this basic version of my data:
NameGradeWeekJim31Sally31Fred32Claire53Susan43Chris52Ted41Jenn53Nicole43
I am trying to figure out How many 3rd grade, 4th grade, 5th graders there will be for each week. I will need a formula to fill in for each blank cell below.
# of 3rd# of 4th# of 5thWeek 1Week 2Week 3
I think I need a formula that would "read" something like:
if "Week" is equal to "1, 2, or 3" then within the rows that contain that week, count all "Grade" that are equal to "3, 4, or 5"
I tried sumif, counta, if, and combinations of them, but I am not sure I am using them correctly.
View 9 Replies
ADVERTISEMENT
May 22, 2012
Building a formula to calculate the total downtime for a specific configuration item (in this example: Wintrack).
See table below:
Task.NumberConfiguration ItemDowntimePriorityINC0028900Wintrack602INC0028900Data Tree201INC0028901Wintrack151INC0028903FAST1901INC0028904Wintrack102INC0028905Wintrack53
The table above will vary in row numbers every month so I would like to use dynamic range. I will be calculating total downtimes for each configuration item every month and would like those totals reflected on a separate Excel sheet. .
View 4 Replies
View Related
Jan 12, 2007
I want to gray out a row of cells (B11:M11) when "x" is entered in cell M11. Suggestions for using Conditional Formatting or a formula would be great. I have tried a couple of formulas but none work.
View 9 Replies
View Related
Oct 21, 2008
I want to set a conditional format to cell A1 with a value in cell P1 When the value in P1 is between 0 and 10 the conditional value in P1 should be set.
Note
cell A1 is also set with a conditional format to put a border around it when there's a value in A1 (cellvalue is not equal to " ")
So the formula referencing P1 would be a second condition.
View 7 Replies
View Related
Mar 28, 2014
I'm trying to find a single conditional formula which summarizes multiple text occurrences in a range of cells. I managed to summarize the occurrences in a single cell, but my challenge is that I'm looking for a single (array?) formula for this.
I have a worksheet with column A which contains the condition and column B which contains a text field. I'm looking for a single formula to summarize all the 'AB' occurrences in the cells B4..B11 where the column A is Apple. I managed to do this by using an additional column (column C), however as my worksheet contains over 10000 rows, I would like to use a single conditional (array?) formula summarize the AB occurrences (multiple ' AB's can occur in a single cell) in stead of having to use an additional field per row.
I have attached a sheet as an example. I'm looking for a single array formula in cell C11 which summarizes the 'AB' occurrences in cells B4..B11 where column A is Apple.
View 3 Replies
View Related
Feb 5, 2013
how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:
For cells M8:EK8, my conditional formatting
condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40
condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39
I want to add:
condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40
condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39
and so on
The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.
resource tracking ex.jpg
View 1 Replies
View Related
Apr 15, 2014
Having trouble changing this formula from its current Median to a Quartile or Percentile. The median formula looks like this:
(MEDIAN(IF(DATA!$B$15:$DZ$15=1000,DATA!$B26:$DZ26))) and works perfect.
However I can't figure out how to manipulate it into a percentile keeping the condition of
IF(DATA!$B$15:$DZ$15=1000)
View 2 Replies
View Related
Oct 11, 2006
I was looking to use the VBA conditional formatting script posted on OzGrid and was curious if it would be easy to make some slight modifications.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Select Case Target
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20 ..........................
I can see that Case is the number range referenced to change the background...However, would it be possible to have it read a cells value as the criteria for the color change? Currently, I would like it to reference values in range I2:M2 and anything matching those cells in range A4:E28 change background to icolor = 30. I'm just not sure what to replace Case with to make it refence cells I2:M2
View 6 Replies
View Related
Mar 14, 2009
I am trying to figure out 3 differnent percentage categories but I cannot figure out what formula or conditional formatting that I should be using. The scenario is as follows:
For example, Jamaal is authorized for 80 hours per month but only uses 15 hours.
What I need to figure out is if he is below 25% of hours funded, then he will be exited from the program. If he is between 25% and 50%, then he will be warned. If he is between 50% and 75%, then he has excellent attendance.
Therefore, I came to the conclusion that:
0.25% x 80 = 20 hours
0.50% x 80 = 40 hours
0.75% x 80 = 60 hours
Which means that Jamaal is in the below 25% category, meaning he will need to be exited, unless he improves his attendance. But what I want to show on my spreadsheet, is a color RED for below 25%, and other colors for the other categories. But I don't seem to be able to conclude it all into 1 cell. I tried using the Conditional Formatting and the below 25% works but once I add a new rule for between 25-50, then my rule for below 25% doesn't work anymore. I don't know if an actual formula exists for this. Does will be a monthly spreadsheet that I will be working off of so it would be nice if all I do is input the total hours used and BOOM it tells me if the client is below 25% hours used or above.
View 9 Replies
View Related
Apr 30, 2009
I have a list of names that belong to a particular group. What I want is to conditional format a sheet so that if a name is entered that does not belong to the group it is highlighted red. Don't really know how to do this though?
View 4 Replies
View Related
Apr 13, 2009
I have a spreadsheet that has a table listing employee names in the far left hand column (A1, B1, C1, etc.). The table has a simple border - black line throughout.
I need the entire row for the employee to change to no border if the employee's name is deleted.
View 2 Replies
View Related
Jan 8, 2010
Using the following conditional formatting formulas:
=(isblank(p99)) then the formatting of (applies to) C99:O99 will be in red and bold
=(d99="Prod.") then the formatting of C99:P99 will be blue and bold
but the results are not as expected. Is something wrong with these formulas?
View 4 Replies
View Related
Feb 15, 2010
I am writing this application wants me to calculate running averages and Std Dev. I will copy a row of values into a spreadsheet. The next row will be a new average of each column. So far so good. But the next time I calculate the new average of the column, I do not want to count the row that represents the last average I measured. SO, I'd like to format the rows that are average values (i.e. bold) and then when I calculate the next average, exclude any value whose font is bold. Currently I am using the following line of code to calculate average:
Is there an easy way to limit the values used to calculate average to only those values that are NOT bold?
Sub ColAve ()
Cells(LastRow + 1, ActiveCell.Column).Value = WorksheetFunction.Average(ActiveCell.EntireColumn)
End Sub
View 8 Replies
View Related
Jan 17, 2008
I am trying to find the slope of column F when Column C is between 4.5-5.5. I can do this by doing four seperate lookup formulas and having the slope function referring to the results. When I try to combine the four cells into a single cell Excel gives me an error. =slope((LOOKUP(5.5,XPos,XPsi),LOOKUP(5.5,XPos,Xpos)),(LOOKUP(4.5,XPos,XPsi),LOOKUP(4.5,XPos,XPos)))
Attached is an example of the data, the functioning slope when spread over four cells, and my attempt at combining the formulas(currently with an ' at the beginning to prevent the error)
View 5 Replies
View Related
Aug 15, 2014
I have a sheet called Quote Summary and a sheet called AssemblyBoms.
In the Bom sheet, I have a named range for QtyPer and a named range for unit price because these ranges are dynamic.
On the quote summary sheet, I want to format the cell (A9 and eventually A9 to A28 and so on), which contains a formula right now that only references a tag and does some equivalence checking
(=IF(AND(QSA>0,QSA=qty1a),assembly1,"")),
to have a yellow background if QtyPer>0 but unit price = "" or 0. I know the formulas for this, but it is not formatting anything. Attached workbook below.
QUOTETEMPLATEMACROTEST.xlsm
Conditional Formula for true:
="AND(qtyper1 > 0, unita1 = "")"
I typed it in without the = or "", but the formatting added those in for me.
I typed a number in the qtyper1 range and left the unita1 range blank to test it and no formatting has occurred.
View 10 Replies
View Related
Feb 26, 2014
I have a column of dates in column M, and in A1 I have the formula =TODAY() for today's date. I would like to conditionally format all of column M (up to row 198) so that it turns orange if the date in M is before today and after or equal to 3 working days before the date in M.
I have this
=AND($A$1<M2,$A$1>=(WORKDAY(M2,-3)))
But it doesn't appear to be working because it's highlighting dates after todays date.
View 6 Replies
View Related
Mar 31, 2009
Have a spreadsheet using the following conditional format to color highlight every other row: =MOD(ROW(),2)=1. Is there a way to apply this conditional format to all columns A through AR and all rows 1 through 24000 only?
View 3 Replies
View Related
Jun 15, 2009
If cell A1 matches a range (lets call it B5:K5) then"T2", otherwise return the actual value of B1. I know it shouldn't be that hard, but I'm a bit blocked.
View 2 Replies
View Related
Nov 26, 2009
I'd like my conditional formatting to format the range of cells red if the range K2:S2>H2. That is when the range K2:S2 are added together, and if their sum is greater than H2, format cells K2:S2 red.
View 5 Replies
View Related
Jan 17, 2010
I have a Conditional Formatting query where in essence I am needing to ‘make invisible’ a range of data by changing the font and background fill to white (and at the same time nullifying the effects of a number of other Conditional Formats). On the surface, this seems an easy thing to achieve, but the problem is, the range I need to apply this CF is a moving range and is dependant on a user changeable date.
Description of worksheet:
The sheet is managing shifts worked over 1 year by 21 people. The top 33 rows are a frozen (and collapsing) display area below which is a matrix of 365 days down and 21 people across. The TODAY button uses a macro to scan the dates and display todays entry directly beneath the freeze line of row 33. The date in M28 can be entered manually and after hitting enter, that dates data is now displayed under the freeze line. Also, using the spin button will increment or decrement the date in M28 moving the entire matrix up or down.
My query..........
What I’m hoping to achieve, is to display ONLY the data selected by the date in M28 (ie that shown under the freeze line, and for all remaining rows to be ‘whited out’, but as the user increments/decrements M28, then the “whiteout” range also needs to increment/decrement. I have whited out a second worksheet “example” as if I had selected 18 Jan (this sheet will not move as there are no buttons, but the data is still there under the whiteout).
I hope this is not TOO unclear, but I would be grateful if anyone could suggest a solution or even tell me if this is not possible – or indeed any other way of achieving the same result ( I had thought of hiding the rows below the freeze line, but the freeze/unfreeze operation would probably be too jerky.
View 9 Replies
View Related
Jan 23, 2013
What I am trying to do, is apply conditional formatting to a range of cells (for example, A2:J2) based on whether the data in one of the cells (D2) contains "Yes" or "No". I would like the entire range of cells to be one color if D2 contains "Yes", and another color if D2 contains "No". If needed, this can be split into 2 different conditional formatting rules.
View 5 Replies
View Related
Nov 18, 2013
I am trying to use conditional formatting to basically unhide a named range when a specific text is input into a range of cells from C23:C32. I have taken the named range "Screw_Test" and changed the formatting to make it hidden. White text on white background, that sort of thing. What I would like is to change the background color and text color back to something visible when the following is met. In C23:C32 I have a drop down. When Pedicle screw is picked in any of the afore mentioned cells, it would trigger the conditional formatting that would change the "Screw_Test" range (D22:F32) as a whole, making it visible. I haven't had much luck.
View 4 Replies
View Related
Jun 11, 2009
I have two columns of data (of varying length). The first column "A" has steadily increasing random values from about zero to 200. I would like to be able to automatically select/highlight the region with values between 50 and 100 as well the corresponding values in the adjacent "B" column.
View 4 Replies
View Related
Feb 27, 2012
I recorded a macro in which I applied a conditional format to a range (the selection).But, when i replay this macro, the conditional format is only applied to the first cell in the range. This can be seen in the "Manage Rules" dialog of Conditional formatting, where the "applies to" column shows only one cell, even though a range of cells was selected by the code.
I set a breakpoint at the first line of code here, and confirmed that the selection is correct, and indeed it is, as in the immediate window, ?Selection.Address correctly gives "$O$6:$O$21".
[BRK, Selection.Address="$O$6:$O$21"]
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=O6/(O6+P6)>25%"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlGray16
[code]....
why the conditional format ends up only applied to cell O6 after the code runs?
"How can i apply a conditional format to a range of multiple cells, based on an expression, using VBA?"
Note that my range is within a PivotTable (but still, this works fine in the UI when i record the macro and the conditional format is correctly applied to the entire selected range).
View 1 Replies
View Related
Feb 12, 2013
I have a range of data on sheet1 say A1:A10 with random letters in each cell. On sheet2, I also have random letters and number in column A. What am trying to do is find a formula which if any of the data on sheet1 can be found in say A1 of sheet2 then shade the cell a given colour or if formula in B2 then show True otherwise false if not found. then do the same for the remainder of the cells on sheet2 which am assuming will be to just copy the formula in B1 of sheet2 or the conditional format in A1 whichever comes first.
View 4 Replies
View Related
Oct 30, 2013
Currently trying to calculate the cumulative performance for a range of months. For example, I have the below figures:
Jul-12
6.59%
Aug-12
1.13%
Sep-12
2.61%
Oct-12
-3.56%
[code].....
I want to create a product if function where if I'm looking back 6, 12, 18 months etc from a specified month (e.g. Sep 2013), it will calculate the product for the range of % (e.g. Apr-Sep for 6 months, Oct 12-Sep 13 for 12 months, etc...). I would imagine it will be =product(1+if(Column A range=6, Column B range))-1.
View 3 Replies
View Related
May 19, 2014
I am wondering if there is a quicker way to set up conditional formatting for a range of cells. In my projects, I have a range of cells N7:U16, where each row (7-16) would need to have a cell formatted based on a value in the cell adjacent to the range. I.e. the cell in range for row N7:U7 needs to be formatted based on the value in V7, and so on down for for rows 7 thru 16. Said another way, if the cell in the row range between n7:u7 = v7, then the cell in the range is uniquely formatted.
But I want to apply this logic all at once, instead of having to conditional format each row individual. The trick is that the cell with the value that I am comparing to is variable (i.e. V7:V16), but does correspond with the row I am comparing it to.
View 3 Replies
View Related
Feb 21, 2007
I have this :
ColumnRow
1 7
1 27
5 3
5 7
5 31
5 35
5 39
5 59
9 7
9 11
9 31
I need a formula to use with conditional formatting that "lights up" the corresponding cells. So, in this case cells A7, A27, E3... should "light up".
Is this possible, or should I use VB?
View 9 Replies
View Related
May 24, 2007
conditional formatting a range of cells where i need more than 3 conditions, so please see below and can anyone kindly construct some vba code for me to do this (And if you have time to explain how it works so i have a good understanding of this)
Range R6 to R299 contains delivery dates
Range AO6 to AO299 is = to the corresponding cells in Range R but formatted to show the Day
Cell AL1 contains a date which changes on a Thursday to show the following week commencing date.
What i am after is code so that Range AO6 to AO299 changes cell colour to the following criteria
less than AL1 (White)
Is between AL1 & AL1+7 (Blue)
Is between AL1+7 & AL1+14 (Green)
Is between AL1+14 & AL1+21 (Yellow)
Is between AL1+21 & AL1+28 (Pink)
View 9 Replies
View Related
Feb 25, 2008
I've been trying to get around the 3 rule limit for conditional formatting,
and I've found the code I need on the site
[url]
I've changed it to suit my needs
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:A2000")) Is Nothing Then
Select Case Target
Case Is = "Test"
icolor = 6
Case Is = "Test2"
icolor = 12
Case Is = "Test3"
icolor = 7
Case Is = "Test4"
icolor = 53
Case Is = "Test5"
icolor = 15
Case Is = "Test6"
icolor = 42
Case Else
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
This works great for one cell, but I need to format a row range based on the single cell.
So if Test is entered into A1, then it changes to yellow, I also need the next 6 cells on the row to change too- In this case [B1:G1].
But it needs to cover the range in the code A1:A2000
View 9 Replies
View Related
