Conditional Statement That Checks For The Value Of A Cell
Jan 30, 2007
I need to write a conditional statement that checks for the value of a cell and assigns a percentage based on the value in the cell. Nothing I have tried works....
Here are the results I need:
If value is 1-3, then 50%, else
If value = 4, then 45%, else
if value = 5, then 40%, else
if value = 6, then 35%, else
if value = 7, then 30%, else
if value =8, then 25%, else
if value =9, then 20%, else
if value =10, then $500
View 9 Replies
ADVERTISEMENT
Aug 10, 2007
I am looking for a conditional format for the following scenario. If A2 has a value less than 1.5 and A3 has a value less than 2 then A1 will show green or whatever. Both of these values have to be met seperatly or A1 will remain unchanged. I just dont know how to put the AND in the formula to include both cell values needing to be met. Newbie to formulas.
View 4 Replies
View Related
Dec 6, 2013
So I've applied conditional formatting to columns A and B to highlight and bold any blank entries. However since those guys tend to show up a thousand rows or so down I don't have anything to alert me that there is a problem. I know that I can just filter those columns to check but I'm not the only one using this sheet so I'd really like to have a visual cue.
Is there a way to have cell J1 highlight if any of the cells in the range (A2:H3000) have been bolded?
View 1 Replies
View Related
Feb 9, 2010
I'm trying to write an if statement that checks if a value is a whole integer value or not. I was initially using the Mod operator but realized that the Mod operator rounds values to whole numbers before determining the outcome. Initially I tried:
View 3 Replies
View Related
May 30, 2008
i am trying to automate altering a DAT file. Basically, each line of data in the DAT file contains an entity name, an account name, and a value. for some of the accounts, i need to change the entity name. for all other accounts, i can leave the entity name alone. if i copy the DAT file into Excel, and use text to columns, how can i create a macro to compare the accounts to a range of cells containing a list of the accounts i need to alter the entity name for, and then change the entity name if there is a match?
the account does not need to match perfectly, the accounts i input only needs to contain the reference account. the list of accounts and the entity name i need to change to are in cells on another sheet. i thought about doing a loop to run through each line of data, but i am not sure how to do that.
View 2 Replies
View Related
Mar 2, 2009
Type class VNOValidation111 Y233 NIf(class=any of the below nos then Vno should be Y if not then N. The result should be in "validation" cell). Class11334466778899100
Im not sure how i can use If function here....
View 9 Replies
View Related
Jan 12, 2007
I'm using excel 2000
I have a formula that checks a cell containing an email address, and if the format is not the office standard, displays a message. We now allow 2 variations, and I want to check for the second condition as well.
Basically, the email address is in the format
"Firstname"."Lastname@here.com
and the formula just checks the address against a lookup of names, the only seperator being period.
I now also need to be able to check for an underscore, and I don't know how to combine the conditions.
The existing forumula is
=IF((LEFT(A2,FIND(".",A2)-1))(LEFT(E3,FIND(" ",E3)-1)),"Check Email Recipient Main Database page column BQ:BR","")
and of course the underscore is easy, too
=IF((LEFT(A3,FIND("_",A3)-1))(LEFT(E3,FIND(" ",E3)-1)),"Check Email Recipient Main Database page column BQ:BR","")
but when I try to combine them, I get an error, or at best a VALUE output.
I think I need to nest them, but maybe there is a way to combine the conditions in or use "OR" and still get the "check email" message" ??
View 9 Replies
View Related
Jul 30, 2007
I'm trying to run a piece of code which checks the length of a cell entry and if it is over 100 characters long it deletes the value. The code is as follows:
Sub Macro1()
If Len(Cells(1, 1)) > 100 Then
Cells(2, 1).ClearContents
End If
End Sub
The string which is causing the error seems to be 255 or more characters long. Any ideas how to tackle the "Overflow" Error?
View 3 Replies
View Related
Dec 19, 2011
I am trying to find a formula for a vlookup that will check one cell for any value but if that cell has no entry then it will check a different cell to return the value how do i do this?
TEAMCSS Wetherill ParkCT NAME
above is where i need it to look CSS wetherill Park is cell C1 the cell that has no entry is H1, both of these cells have validation lists in them,
OS windows XP excel 2007
View 3 Replies
View Related
Apr 14, 2008
I have to determine based on average number of weeks on hand whether an Item is Overstock, Obsolete, or Normal. I've got that taken care of with some If statements (I'm sure there's a cleaner way to do this but I've not figured that out yet.)
But I need my if statements to be conditional to whether or not an item is seasonal. with 10,000 active SKU's Its a little bit much to manage those in my excel copy of the item table so I wanted to make an exclusion table where I could just key the item in and the months it would be considered active in stock. This needs to go into the If statement on the items table that looks at the current date and says if it's less than six months old it's a "new item" but if it's six months and a day then it's a normal item, then ignore both if it's seasonal and just be a seasonal item if it is on the exclusion worksheet.
I'm attaching this incase anyone can offer any ideas. It would be much appreciated. I had to delete a ton off of the spread sheet to get it to upload, and the data is dummied to keep company inventory confiential.
View 14 Replies
View Related
Jul 2, 2007
I have a column with different letters in it. If this column DOES NOT have an L, H or F than I would like it to add a K.
If the column DOES have a an L, H or F then I would like it to remain as is and not add the K.
View 10 Replies
View Related
Jul 30, 2014
I wish to use a formula to highlight a date based on two criteria in two separate columns;
The first is the Date that will be in Column B.
I want it to highlight the date if : Today() is greater than that date in the cell plus 5 years (or 1824 days)
The second criteria I want is for the date to be only highlighted if the corresponding cell (in column A) does NOT contain the term ("Basic")
I have the formula:
=AND((TODAY()>(B1+1824)),A1=("Basic"))
which highlights the date correctly IF the term in Column A is "Basic" : But I cannot figure out how to insert the NOT statement to make the formula flip around so it only highlights those dates older than the 5 years requirement and NOT contain the "Basic" in column A.
View 2 Replies
View Related
Mar 30, 2009
I have several rows with data in and what I would like would be to total the amount of time "CU" is displayed and if that number is less than say 10 the formatting is applied. I need if possible to be able to do this via the one condition.
View 2 Replies
View Related
Oct 14, 2011
I type this formula and it work only for the first and the last condition, for the other condition it return to me FALSE. i dont now what is wrong,
=IF($C$10=1200,$E$10
View 1 Replies
View Related
Apr 11, 2014
Can you do a IF statement in Conditional Formatting? I need a column to highlight Red if there is a blank in it based on the current time COMPARED to the stated time in another column (on a separate tab)
So if current time is 1:30 PM and the stated (appointment) time on the other sheet is 1:15 PM, and my control field is still blank, I need it to turn Red.
View 1 Replies
View Related
Oct 6, 2008
I guess because I use a drop down box and another cell reference for the date, my IF/AND statement isn't recognizing the date and therefore gives me a "FALSE" result. I have the date formatted as a date.
I've tried referring to cell I2 which is the drop down box - that doesn't work. I've tried using quotes around the date as if it were text - that doesn't work. If I use just the w10=3 part, that'll work fine but it must be associated with the proper week otherwise every 3rd of the month that falls on a friday will be a holiday.
View 3 Replies
View Related
Feb 12, 2009
How do you do two "if" statements?
I need to have two tests before i get a final result so i need to know the protocal to get two if statements.
View 14 Replies
View Related
Aug 18, 2009
I want to conditional format cells with an if statement (something to that nature). I attached an excel file to aid in my question. Basically I want it to be color coded to alert users Red if no action is required because info is left out Yellow if info is available so take action Green if all is complete and nothing needs to be done.
If G2 does NOT have data then cell H2 has red fill
If G2 does have data then cell H2 has yellow fill
And finally if I2 is filled out cells A2:I2 are green filled
View 5 Replies
View Related
Dec 30, 2009
I have a list of 31 values in separate cells. The values are three digit letter codes. Here is an example of 5 of them:
ATL
GMI
WOB
COI
PTS
What I am wanting to do is have a Conditional Format to change the color of the cell to Pale Blue if the value equals another cell that is within a range.
View 4 Replies
View Related
Feb 20, 2009
I am trying to find a way where I can just enter the information into one and have it flood to the other.
worksheetA is the master sheet which contains data on all accounts - the order and number of entries on this account changes monthly.
worksheetB shows data only on a single account.
What I am looking to do is to take the account name from worksheetB find the data I entered in worksheetA and have that flow to the apporitate cells in worksheetB.
Something like this
cell on worksheetB displaying number of vehicles - if worksheetB(cell with the account name) = worksheetA(range of cells with multiplenames) if the two match somewhere on the list then display the number of vehicles from a cell in another coloum same row.
sorry if its confusing, tried my best to explain it. I'm sure it can be done with VB but just don't know that one much, is the syntax and language similar to the old ms basic?
View 7 Replies
View Related
Jul 9, 2007
1. Pupils have a target grade
2. Pupils can either do a higher or foundation Tier exam depending on their ability
3. Pupils do 9 modules and the grade bounderies are different for each module.
3. I use bar charts to report the results back to pupils and parents. Instead of just telling them you got 50% D your Target is C. I also want the target grade to be converted to a mark.
I do not want to create another woorksheet inorder to use Vlookup.
4. I now know that Index and Match can do the trick of reading the left columns. But I cannot get them to first consider the Tier for each student.
I was thinking of usind IF
IF Tier is Higher then Index the Higher Table and Match else Index Foundation Table and Match to get the mark. But I do not know how to express this in a way that's readable by Excel
View 10 Replies
View Related
Aug 9, 2012
HOW I CAN USE IN EXCEL VALIDATION DROP DOWN BELOW CONDITION
=IF(B1="Assets",[Mapping.xls]Assets!$A:$A,IF(B1="Liability",[Mapping.xls]Liability!$A:$A,""))
View 7 Replies
View Related
Jan 10, 2013
I have the macro below that I want to use to conditionally delete all columns where the first row of each column does NOT have the following values but it seems to be deleting most of these columns as well all the others.
Fellow Excellor
Sub Deletecolums_Conditional()
Application.ScreenUpdating = False
[Code]....
View 3 Replies
View Related
Feb 25, 2013
In s74 I have the formula
=COUNTIFS(C:C,"Holiday Inn Express Limerick",I:I,1)
And it gives me a count of 21. Is there a way where I can use conditional format to highlight those 21 instances?
View 2 Replies
View Related
Oct 21, 2013
So, Excel reads my formula. It tells me it's correct, but it has too many statements. [I know this is true, because the equation is absolutely huge. )
The thing is, I need it to tell me, based on two separate columns - one being "State" and the other being "A vs. O", who this contact belongs to. [For each state, there is a different person for A and for O, and the entire spreadsheet is text.]
My ridiculously large, almost functional function is:
=IF(AND(RC[9]="WA",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="WA",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="MT",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="MT",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="ID",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="ID",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="OR",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="OR",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="CA",RC[13]="APP"),"Tom Wieske"),IF(AND(RC[9]="CA",RC[13]="OPP"),"Tony
[Code] ....
As you can clearly see, there are a lot of terms, but I need both the "State" and the "A vs. O" column to determine the outcome of the column the formula will be in.
View 3 Replies
View Related
Dec 28, 2007
Set MyRange = Range("A1:AZ9615") ' Range to apply format to
For Each Item In MyRange
Select Case Item.Value
Case "1780", "1800", "1810", "2050", "6170"
Item.Font.ColorIndex = 3
'x = 3
Case Else
x = xlNone
End Select
Item.Rows.Interior.ColorIndex = x
Next Item
I want it to search that range and turn those Numbers in Quotes to Red, which it works fine, but Somtimes those numbers are Imbedded in a string excample "1810-1-DAV". So my Question is When It turns my normal numbers red, how can I get it to turn the STRING RED ALSO?
View 9 Replies
View Related
Apr 25, 2008
I am trying to eliminate the use of formulas for conditional formatting in my code. I heard that the CASE statement might work for this. Below is the code I have but the case does not like the second case statement.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range(("S2:V" & TTRows), ("W2:W" & TTRows))) Is Nothing Then
Select Case Target
Case Is >= 0.8
icolor = 3
Case is >= 0.7 and not >= 0.8
icolor = 6
Case Else
icolor = 0
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
View 9 Replies
View Related
Nov 17, 2004
I want to copy a row based on a certain condition. I have 700 rows of data on sheet 1. If Cells in column A contain an X, copy row A# to sheet 2. If there are more than cell that has an X copy all of the rows, naturally. Can this be done with a simple conditional statement, or am I going to need to generate an array, then write that array to sheet 2?
View 4 Replies
View Related
Mar 24, 2014
Conditional formatting. I have been tasked with creating a simple game for a group training project.
The game consists of users filling in the answers to questions (simple one word text answers). My boss wants to have a master sheet with the correct answers, and a subsequent sheet that will then compare the employee's answer to the master sheet. If the employee's answer is correct, then the output sheet simply displays the correct answer, however, if the employee's answer is wrong, he would like the output to display the correct answer and highlight the cell.
Is there a way to have the output sheet highlight a cell only when the answer on the employee sheet is different from the master sheet? I was thinking along the lines of using an if statement to see if the values of the employee sheet differ from the master sheet, but I do not know how I would then use that result to highlight the cells in the output sheet.
View 1 Replies
View Related
Mar 25, 2014
I created added conditional formatting to the Response Due column, to keep track of when response was due.
I only want the conditional formatting to be applied if the cell next to it is blank.
For example, the last one on my attachment is due 3/26/14 but I have already submitted a response (see column K)
What can I add so that only those dates next to a blank in column K get the conditional formatting?
Sample.jpg
View 2 Replies
View Related
