Cell Formatting Using If Statements?
Apr 2, 2013
Is it possible to format a cells properties based on the value of the cell? I want to automatically format numbers that fall within certain ranges. For example, if I enter a number less than x, the number will be bold, in a blue color. If I enter a number greater than y, that number will be bold and red. All numbers outside the specified ranges would remain the default font. I would prefer that this property be set for a specified range of cells prior to entering any data, and automatically format as I enter the values.Can it be done?
View 8 Replies
ADVERTISEMENT
Mar 22, 2013
I'm trying to analyse all of my bank statenments to see where all my money went!! I've got online banking so I thought it would be a relatively easy process of copying and pasting each month into excel.
Well, that in itself was easy, the problem is with the formatting of the financial numbers. Excel doesn't seem to recognise them as numbers, so at the moment I can't do any manipulation with the numbers. I have tried everything such as:
Format Cells > Number > Number and Currency
Copy and Paste Special > Values only
Copying into Notepad and back into Excel
Even if it appears to have changed the numbers to 'number' or 'currency' formatting by right justifying the numbers, if I try to add up the numbers using the SUM function, it doesn't want to know.
For some reason nothing seems to work - what am I missing?
For an example, I have attached a snippet of the spreadsheet with my starbucks transactions.
View 3 Replies
View Related
Dec 1, 2008
I need some help with applying some conditional formatting to cells that are populated with logic. I attached a portion of the sheet I am working on.
The cells in rows 41 and 42 will always be there so I am ok with them. The data in cells C43:I43 is loaded if a certain feature is selected on a separate sheet. If it is NOT selected, I need the values to not show up, (this part is already done with the formula in the cells) and i also need the conditional formatting to make all the cells white or blank. The same is true for the data in cells C46:I46
View 10 Replies
View Related
Jun 12, 2007
I have a spreadsheet that has several columns containing names and data. In the first column, is a person's name, in the second, is a client number for that person, and in the third is the "last contact" date. I have a fourth "reference" column that has a conditional statement I was trying to use for formatting. For example, in A1 would be "John Doe," in B1 would be 987, and in C1 would be 06/10/07. The next row would have data for a different person. In column E I have my reference column.
If the last contact date is less than 4 days from the current date (which is in cell K2), the reference column (using an if formula) will produce the number 1. If it is 5 or 6 days from the current date, it produces the number 2, and if it is 7 or more, then it produces the number 3. Here is the exact formula:
=IF($C2+7<=$K$2, "1", IF($C2+5<=$K$2, "2", IF($C2+4>=$K$2, "3")))
What I want to happen is this: if the reference column produces a 1, then I want the corresponding row to turn red. This would only include the name, assigned number, and last contact date. I do not need the entire row to change a certain color. If a 2 is produced, I would like the row to turn yellow, and if a 3 is produced, I would like the row to turn green. This is going to run for many rows (up to 150). How do I do this using VBA? Keep in mind the numbers in the reference column will change if the "last contact date" is updated to a more current date.
View 6 Replies
View Related
Aug 2, 2012
I am having difficulties trying to get some conditional formatting done.
For the below i need to highlight the 2012 YTD Column based on the following conditions, and i cant get the formula correct
GReen = If 2012 YTD meets /beating both 2012 target and 2011 Actual figures =And(D2
View 1 Replies
View Related
Aug 20, 2009
Can you have IF and AND statements in Conditional formatting formulas?
I put this and I get an error: =IF(AND($G5=""($H5=<>"")) I was testing the water for adding and OR statment also. I really want if cell G5 is blank and cell H5 or I5 has text, then G5 should be red.
View 12 Replies
View Related
Nov 22, 2008
I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.
View 2 Replies
View Related
May 26, 2008
Sheet one will contain the following:
DATE A B C D E
2/22/2008TRUEFALSEFALSEFALSERon
2/23/2008FALSETRUETRUETRUEPhill
2/24/2008FALSETRUEFALSEFALSETracy
2/25/2008FALSEFALSEFALSEFALSESharon
2/26/2008TRUETRUEFALSETRUEBill
On sheet two I need to list any date that has three or more true statements with the coresponding name.
EXAMPLE:
2/23/2008Phill
2/26/2008 Bill
View 4 Replies
View Related
Oct 14, 2009
How can I combine the two below statements like these to make one "Or Statement"? Sorry...I haven't done excel and/or's in forever?
=IF(('Proj Info'!L10="main")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
OR
=IF(('Proj Info'!L10="serv")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
View 2 Replies
View Related
Aug 10, 2006
I use macros to print pages, depending on the number of entries I have. If I have 1500 entries, I have to have 1500 If statements. Is there a way to write VBA in a macro to where I can refer to a cell and use the value of that cell to print the range.
View 5 Replies
View Related
Dec 4, 2008
I am trying to using multiple if staements regarding one cell. What I am trying to achieve is:
if b2=0, then null, however if b2=1, then arable, however if b2=2, then manged grassland, however if b2=3, then forestry/woodland, however if b2=4, then semi-natural vegetation, however if b2=5, then urban, however if b2=6, then water
I have these 2, but they don't work - what am i doing wrong?
=IF(B2=0,null, IF(B2=1, Arable, IF(B2=2, Managed_Grassland, IF(B2=3, Forestry/Woodland, IF(B2=4, Semi-natural_Vegetation, IF(B2=5, Urban, IF(B2=6, Water, NO_LAND_USE)))))))
=IF(B2=0,IF(B2=1,IF(B2=2,IF(B2=3, IF(B2=4,IF(B2=5, IF(B2=6, Water, null), Urban),Semi-natural_Vegetation), Forestry/Woodland), Maneged_grassland), Arable), null)
View 5 Replies
View Related
Oct 14, 2013
I want to create a formula that will turn B2 in floors if A2 has /1 with its formula, Ceilings if it has /2, walls if it has /3, and M&E if it has /4. For example 456-401/2-1569 in cell A2 will populate ceilings in B2. I know I can create If statements but I can only figure out how to do this to search for one text and populate one word. I know Vlookup is possible but this spread sheet will be blank and as people populate there information I need the cells to automatically populate the data into the B column. how I can create the right formula?
View 4 Replies
View Related
Feb 16, 2010
Can you have multiple IF statements in one cell. I know nested IF's are out there, but they will not work for what I need.
I just need to have two (or more) complete IF statements.
View 9 Replies
View Related
Apr 25, 2014
When I enter my sales data into a sheet it can be 10000 rows long, I want to be able to enter a set number of transactions on a second sheet which then uses a formula to look up what items was sold on said transaction.
I'm pretty sure it's possible but I'm out of my depth. I've using something like it before which was this statement - =IF($B1566="","",INDEX('RMS Sales'!P:P,MATCH($C1566,'RMS Sales'!$A:$A,0),1))
I've attached example sheet : For-Excel-Forum.xlsx
View 12 Replies
View Related
Jan 28, 2014
Im' working on an ROI spreadsheet at work and looking to add multiple IF statements leveraging one cell. an example is this:
If E51 is > 1 and < 100001 then the cell should equal "PREMIUM"
If E51 is > 100000 and < 175001 then the cell should equal "PREMIUM PLUS"
If E51 is > 175000 and < 250001 then the cell should equal "ENTERPRISE"
I have a formula already prepared in the spreadsheet that will come up with a value anywhere between 1 and 250,000 in cell E51. I need that cell value to be PREMIUM, PREMIUM PLUS or ENTERPRISE; depending on the value and can't work out the correct formula.
View 2 Replies
View Related
Oct 6, 2006
I’m trying desperately to get this if statement to work
If Workbooks("Master.xls"). Sheets("intro"). Range("A2") <> "" And _
Workbooks("Master.xls").Sheets("intro").Range("B2") = "" Then
For Each vaFileName In .FoundFiles
ProcessDataCrit1 vaFileName
Next
If Workbooks("Master.xls").Sheets("intro").Range("A2") <> "" And _
Workbooks("Master.xls").Sheets("intro").Range("B2") <> "" And _
Workbooks("Master.xls").Sheets("intro").Range("C2") = "" Then
For Each vaFileName In .FoundFiles
ProcessDataCrit2 vaFileName
Next
If Workbooks("Master.xls").Sheets("intro").Range("A2") <> "" And _
Workbooks("Master.xls").Sheets("intro").Range("B2") <> "" And _ ..................................
Basically what it should do is to start the code ProcessDataCrit1 if there is a value in A2 but not in B2 or C2, start ProcessDataCrit2 if there is a value in A2 and B2 but not in C2 and start ProcessDataCrit3 if there is a value in all three cells. The formula is working for ProcessDataCrit1 as soon as there is a value in B2 the formula is not working.
View 2 Replies
View Related
Feb 13, 2014
I am using Excel 2007 and am attempting to write multiple IF statements within a cell. This multi-cell validation would be used to validate that A=B and C=D but A&B do not equal C&D as illustrated below:
ABCD
1111FALSE
1110FALSE
1101FALSE
1011FALSE
0111FALSE
1100TRUE
0110FALSE
0011TRUE
1001FALSE
1010FALSE
0101FALSE
1000FALSE
0100FALSE
0010FALSE
0001FALSE
0000FALSE
Here's the formula that I attempted to use:
=IF(A2=B2, (IF(C2=D2), (IF(A2=C2), "Error3", "OK"), "Error2"), "Error1")
However, I receive an error message stating "The formula you typed contains an error" and it highlights the D in the equation.
I am not sure why the D was flagged as an error. If anything, I thought the "Error" portions would have been flagged...
Is it possible to do a multi-cell validation in Excel?
View 6 Replies
View Related
Oct 21, 2008
I'm trying to copy one cell data from one sheet to another if it matches a ciritra but need multiplue arugments
i.e
if A1,sheet1 = Yes then I need to copy B1sheet1 into a say,a1sheet2, but if A1 = No then copy b1sheet1 into b1 sheet 2
So copying data from one sheet to another sheet, the if true copy to one cell in that sheet if false copy to another cell in that sheet
View 9 Replies
View Related
Jun 19, 2009
I've got the following code, which evaluates a sheet for the string "Event held", and then offsets multiple columns before adding several strings.
The second part of the code is what I can't get working correctly. Each cell containing the strings inserted by the offsets are set to change colour based on their contents.
What I want is for them to change colour as soon as the strings are offset, but at the moment, I have to click on each cell before the colour appears....
View 9 Replies
View Related
Mar 6, 2014
I have a spreadsheet that i want to create a part number that contains 4 parts. Ex 1rl9. The cell i want this to be in is blank. What formula do i use to make the second character in the blank cell = r?
View 9 Replies
View Related
Sep 24, 2013
I have one column that contains a monetary amount (column AQ) , and another that contains text reading either "inflows" or "outflows" (column AC)
When AC says "inflows", AQ should be positive, and when AC says "Outflows", AQ should be negative.
I need the text in column AQ (the monetary amount) to become red when the the opposite is true.
i.e. When AQ is negative and AC says "Inflows", AQ should become red. And when AQ is positive and AC says "Outflows", AQ should become red.
View 2 Replies
View Related
Oct 9, 2013
I have two columns. In column B is the date of "last check". I column A is the date of "next check". I would like to have cell A2 in yellow color 334 days after the date entered in cell A3 and than in red color 365 days after the date entered in cell A3. Same thing for cell B2 related to date entered in cell B3. Yellow color in cells announces that check will expire within 30 days and red color that check has been expired.
View 1 Replies
View Related
Apr 14, 2013
This is a conditional formatting problem I have not been able to resolve:
I have a range, say A1:N30
Each cell contains a text/number combination.
I would like to highlight each cell on a specific row if the vale exists within the row above - the issue is that the cell values are not in the same columns.
I have attached a sample workbook with the desired output.
Conditional formatting.xlsx
View 2 Replies
View Related
Apr 1, 2014
I am trying to alert our purchasing mgr when order dates are approaching or not meeting our project deadline.
As of now i have the following rulesif order date is due today or past due - redif order date it greater than project date - redif order date is due within 2wks - yellow
Now all I need is a rule where there is an order without a due date but the project deadline is within 2wks (yellow) and past due or due today (red)
KO_04.01.14.xlsx
View 3 Replies
View Related
Aug 12, 2014
I have a column who's content is determined via about 6 nested if statements from data on that row. That cell is then conditionally formatted to a certain color based on the text that is ultimately printed from the nested ifs (simply an extra visual legend for the text). All of this contributing info and about 1000 lines items make a very large and difficult to print page. What I am trying to do is a make a summary sheet that simply takes the index number of these 1000 rows and copies or links the conditional formatting of the mentioned cell onto this number on another sheet. I have already linked this status cell and put it adjacent to the index number which works well in that with two columns I can show the index and status but if I could combine the color of the status cell onto the index cell, it would be even better. I am pretty sure another conditional format for this summary sheet would not be possible or be extremely complex since the contents of the index cell I am conditionally formatting have no bearing on the conditions for the format. Was hoping there is some VBA magic that could simply mimic the conditional formatting from one cell and put it on another.
View 1 Replies
View Related
Aug 13, 2008
I have Excel 2000, recently when I format a cell to display mm/dd/yyyy and enter mm/dd/yy, it is displaying dd/mm/yyyy. Or it starts out correctly and during a future opening of file it displays incorrectly.
View 2 Replies
View Related
May 9, 2013
I'm using Excel 2010 and I would like to format a cell (say, the font of that cell turns RED) if the value of another cell meets a certain criteria.
View 9 Replies
View Related
Jul 28, 2009
I would like to format cells with a currency based on a value selected at the top of my worksheet. Cell C3 is a drop down of currencies. And I would like cells range E11:E200 to format in whatever currency selected in cell C3.
View 9 Replies
View Related
Sep 1, 2009
I've set a conditional format to flag the row green if cell e2 has a Y or a N in the cell.
Currently i have the following conditional format formula is as
=IF($E$2="y",TRUE,FALSE) this is set to change row to green
=IF($E$2="n",TRUE,FALSE) this is set to change row to red
My Questions =
What i want to do is copy this formula through the 500 or so rows and have the formula adust to
=IF($E$3="y",TRUE,FALSE) For row 3
=IF($E$4="y",TRUE,FALSE) for row 4
=IF($E$5="y",TRUE,FALSE) for row 5
Ect.
I've tried using the formate painter but all it does is set all row to conditionally format dependant on what is in Cell E2. Thoughts on how to copy this formula is conditional format so i don't have to manually adjust it line by line. Doing this 500 times over would be a pain.
View 3 Replies
View Related
Feb 6, 2013
I would like to create a cell that will tell me if a dimension has failed.
I have a tolerance that the value must be between and if it is not then i would like a cell that will input an X
View 7 Replies
View Related
