Conditional Formatting Based On Numbers And Texts?
Jun 27, 2014
I have column A with Different status like Open, closed, available etc. Column B have numbers. i am looking for a formula in condition formatting for column B.
If the cell value in column B is 2 or more, the same cell should be highlighted in Green.
If the cell value in column B is 1, the same cell should be highlighted in Yellow.
If the cell value in column B is 0, the same cell should be highlighted in Amber.
If the cell value in column B is less than 0, the same cell should be highlighted in Red.
If the cell value in column A is "Available", cell in column B should be highlighted in Amber.
View 5 Replies
ADVERTISEMENT
Apr 29, 2009
I've a cell that contains numbers and texts and I want to sum the numbers and get a result in another cell.
This is an example for my cell:
arando-3,12; hegymeg-4,3; vas-7; aranyoska-1,8
and the result: 16,22
View 9 Replies
View Related
Aug 23, 2012
My excel sheet contains inventory of products and quantities, each is in a form of combined number followed by text without a delimeter. For example 2AWS means Two (2) of AWS.
Each cell in my excel database may contain pair of products, each is defined in the above form but the pair of products are separated with a comma.
I need to calculate the total number of each product and split in two different cells.
For example:
Cell A1: 3GFG, 2AWS
Cell A2: 2NG, 1AWS
Cell A3: 1NG, 4GFG
Desired Output
==============
GFG7 (i.e. Cell A5: GFG , Cell B5:7 etc.)
AWS3
NG3
View 3 Replies
View Related
Jun 25, 2014
We are changing management systems here and in order to do so we need to provide the names and addresses of over 400 different people. Our current management system stores people's addresses as a whole for example; 32, Example Street, Town, County, Postcode.
I cant extract just the numbers because I only need the house number and not the postcode.
After searching several forums I thought I had solved it with this.
Cell A1 is the extracted address from current management system reading 32, Example Street, Town, County, IU13 8TY
Cell B1 is =LEFT(A1, SEARCH(" ",O15,1))
Cell C1 is =SUM(MID(0&B1,LARGE(ISNUMBER(--MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1))*ROW(INDIRECT("1:"&LEN(B1))),ROW(INDIRECT("1:"&LEN(B1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B1)))/10)
That only gives me the number "3" though and I need 32.
Basically A1:A400 will be addresses as above and in cell B1:B400 I need just there house number at the start.
View 3 Replies
View Related
May 7, 2013
I have excel data where it runs like below:
Lumina
006
Ali Rashid Al Amin
61.40
Date
03/04/2013
11/04/2013
14/04/2013
check
20
Raja
I need a result column containing as below but where the numbers are removed those cells has been
Lumina
Ali Rashid Al Amin
Date
check
Raja
View 4 Replies
View Related
Mar 20, 2013
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
View 3 Replies
View Related
Jun 26, 2013
I'm designing a spreadsheet for our department which tracks a students progress throughout their time at the school. There are 4 levelsStatementSchool Action PlusSchool ActionMonitorIn year 7, they may come into our school at Monitor level, but when they get to year 8 they may have moved to Action Plus. I want to highlight that change on my spreadsheet with conditional formatting if possible so if they go up, the cell will turn red, if they move down the list the cell will turn green.
I've created two columns on an options sheet (which holds all my data validation lists) and assigned a number to each of the levels so i can conditionally format my columns using < and > but I don't know how to write the lookup formula
What I'm trying to achieve
ExcelHelp.jpg
My Options sheet
Excelhelp2.jpg
View 3 Replies
View Related
Jan 27, 2009
I found out about conditional formatting for five different letters or letter combinations, using VBA, using the code below.
I was wondering if this is also possible with letters or letter combinations, BV, RV, SV, CV, Z and numbers, somewhere between 0 and 9,5.
View 12 Replies
View Related
May 5, 2009
I have a sheet that has conditional formatting that looks at column D2 for a number and divides that by half. it then colors in a corresponding cell on a row in the sheet that matches that number. the problem i am having is with odd numbers like 23 it gives a result of 11.5 . all my cell numbers are all rounded to whole numbers. i need to round the 11.5 to 12 or 13 it does not matter which one... i just need it to round it up or down. I have tried everything i can think of but have had no luck. I attached my workbook so you can see how it works.
View 4 Replies
View Related
Apr 23, 2014
I have a number in column "E" T13245v1
I have a formula in column "D" =RIGHT(E1459, 1)
Which gives me "1" in column "D" (last number in string)
In column "P" I have a number which I want to apply conditional formatting to, to highlight if the numbers in "D" and "P" do not match.
What is happening is that the conditional format is seeing the formula not the number and therefore always applies the condition. How do I correct this.
View 2 Replies
View Related
Feb 14, 2007
suppose I have the numbers 1-25 in a column. I want to color the numbers 1,4,7,10,13,16,19,22,25 green, color 2,5,8,11,...25 orange and color 3,6,9,12...25 red. I can not set up the conditional formatting formulas correctly.
View 2 Replies
View Related
Aug 24, 2012
My excel database sheet contains inventory of products and quantities, each is defined in the form of combined number followed by text without a delimiter. For example 2AWS means Two (2) of AWS.
Each cell of the inventory field may contain up to two products, each product is identified in the above format; but the pair of products are separated with a comma.
The inventory field is occupying from cell $A$2:$A$5000; and is defined as a named range "INV_QTY" with scope within the entire workbook. I need to calculate the total inventory for each product.
Here's an example:
Cell A2: 3GFG, 1AWS
Cell A3: 2NG, 2AWS
Cell A4: 3AWS
Cell A5: 4QLK, 4GFG
etc.
The desired output should be calculated as follows (assume Input will be on Column C & D)
Product - QTY
GFG - 7 (i.e. Cell C2: GFG , Cell D2:7 etc.)
AWS - 6
NG - 2
QLK - 4
View 7 Replies
View Related
Jan 28, 2014
I have some data referring to % that comes in the format of 1.1% for a positive number and (1.1)% for a negative number
How can i get the 1.1% to display green and the (1.1)% to display red?
View 1 Replies
View Related
Feb 19, 2014
I need the conditional formatting to make all numbers that are zero clear (i.e. no fill).
I need it to make all negative numbers to be red, however it doesn't seem to recognize "-1" as a number, and ends up highlight everything red when I say "highlight values < -1 red".
How would I do this?
View 2 Replies
View Related
Mar 6, 2013
formula I colored only if three numbers in a list.
attach file
formattazione.xlsx
View 5 Replies
View Related
May 18, 2013
I want to have Excel highlight one or two numbers/cells in one column based on the value in another cell. I am attempting to do this using Conditional Formatting.
For example, if X1 is BETWEEN A2 and A3, I want to highlight A2 AND A3. I have used various AND and IF functions to achieve this goal.
Also, if X1 is EQUAL to A2, I want A2 to be highlighted. This is easy to do with a simple formula.
But my problem is this: if X1 is equal to A2, I want ONLY A2 highlighted. If X1 is between A2 and A3, I want BOTH A2 and A3 highlighted. I cannot figure out how to achieve both. For example, when X1 = A3, A2 AND A3 are being highlighted, instead of just A3. I would prefer to do this without a macro, if possible. Can this be done?
Example formulas I have tried which easily achieve the highlighting of two cells when the value is between them:
Condition 1=IF(AND($I$35>I8,$I$35<I7),1," ")+IF($I$35<>I7,1," ")
Condition 2=IF(AND($I$35<I6,$I$35>I8),1," ")+IF($I$35<>I7,1," ")
It appears that the +IF, etc. functions are not doing anything.
This is more complicated by the fact that the value of, in the above example, I35 is based on a value from another sheet (I used a function to transfer a value from one sheet to I35 in the other sheet). As data are entered on Sheet1, the value in I35 in Sheet2 changes.
View 14 Replies
View Related
Oct 16, 2013
I have a table that has labels of departments. Then each of those departments will be tracked per week on a number. I am looking for a formula for conditional formatting that if cell C5 is less than B5 that cell C5 (with the smaller number) fills green. Then depending on the next weeks number it will have no fill or a green fill if the number has decreased in cell D5.
Is there anyway that I can do an array of cells or do I have to do it individually per column?
View 2 Replies
View Related
Jul 13, 2014
What would be the best way to apply conditional formatting where you actually want to apply a background and foreground colour to cells that have many different item numbers, ex:
6201 = Dark blue font, light blue background
6202 = Dark Green font, light green background
6230 = Dark Purple font, light purple background
6245 = ...
6445 = ...
6447 = ...
6448 = ...
and the list goes on for about 200 different colour combinations.
I know I can do this in conditional formatting using the ribbon, but I wonder if there was a faster way to do this
View 6 Replies
View Related
May 5, 2014
i wish to do some conditional formatting. my target for the first 6 months from date activated is 50,000 after 6 months is 100,000. and for those below target, i will need it to be highlighted in red. thus i have 2 sets of conditions.(calculate the no. of months and the revenue).
View 2 Replies
View Related
Mar 25, 2013
How do I do "conditional formatting" to high light the numbers which bigger than average + 2 * stdev in a data group?
View 4 Replies
View Related
May 22, 2014
I m unable to apply conditional formatting with numbers ( font and fill of same color).
like:-
if press 1 , cell and font should be of same color and if 2 with different color and so on
as of now either formula is applying on cell or font but not on both
View 1 Replies
View Related
Apr 27, 2013
Excel 2007 Conditional Formatting. I am trying to apply conditional formatting to a wide range of cells. I only want the rule to adjust the color and shading, under the FONT tab in the section below. Specifically, I do NOT want to adjust the number format (I have the user select the scaling and use VB to populate the number formatting). I accomplish this by removing the number formatting by going into the conditional formatting rule, clicking on format, and then on the first tab (Number) -- on the bottom right there is button to Clear.
This resolves the issue perfectly, and I can operate the report exactly as I would expect.
Unfortunately, when closing the report (after a save), and then re-opening, the conditional formatting number format is no longer cleared. Worse yet, because it no longer is cleared, the scaling VB doesn't work, unless I go back into conditional formatting rules, and go to the number tab, and click clear.
Screen shot of after re-opening, no changes:
Report looks terrible (not real data):
View 2 Replies
View Related
Feb 2, 2012
I currently have a spreedsheet which has a conditional formatting cell where if the figure is above 1 is green and below red what i am trying to do is get a sum to calculate based in its color?
I.E. I have a value in cells F7+F25+F27+F29 i then have a value in F17 but only want this to be included in the total calcualtion if the figure in E15 is equal/above the value of 1.
View 3 Replies
View Related
Jun 18, 2014
I have a worksheet ("ALL JOBS") with huge row data and the row data will vary every day. My problem is there will be a lot of blank cells which I need to fill manually and its tacking lot of time of mine.
Code to fill these blank cells automatically with some specific texts based on the attached conditions.
I have attached the workbook for more details. FillCellsTest.xlsm
View 10 Replies
View Related
Jul 29, 2014
In Excel, I want to do conditional formatting on a cell based on the value of another cell. For example: in cell A1, I have text. I want this cell to have a grey background if the value of cell B1 is less than or equal to 1/31/14 and to have no fill (default) if it is greater than that date.
View 8 Replies
View Related
Aug 18, 2014
On the attached worksheet I'm looking for the squares in F4:F6 to be coloured corresponding to the colour names in D4:D6 using conditional formatting. I just can't get it to work today.
View 1 Replies
View Related
Mar 30, 2014
I have a worksheet which has the years 2001 to 2013 on in row1. I am trying to find a way of highlighting the entire row in green if a specific year contains an "a" (Marlett, tick). The complex part is that I would like the specific year to be determined by an entry input into cell A2 on sheet 2. See Attachment for reference.
View 2 Replies
View Related
Apr 9, 2014
I am working in a table where I would like the whole row to highlight based on the value in one of the cells. These rows of data will be updated regularly, so the formatting has to change with the data.
View 13 Replies
View Related
Feb 17, 2010
I want to create a conditional formatting based on a cell in a row. If the value of that cell is greater than or equal to $20 the entire row would be highlighted with a color.
the main thing is that the cells in the rows have if formulas that supple the data for the cells. The cells will be blank if nothing is to be displayed but they will still contain the formula.
View 12 Replies
View Related
Jun 4, 2012
I want to format a cell conditional on the value of another cell. I have read numerous threads on the topic and no matter what I do, it doesn't seem to work for me. I've made it a simple as possible by creating a new workbook and entering:
A1: 5
B1: 2
C1: Some random text - this cell has the conditional formula: ="A1>B1"
However, the cell doesn't get formatted. Here is a screenshot and here is the simple workbook.
View 7 Replies
View Related
