If Condition Formula 


If Condition Formula  Excel 
View Answers 
How can I make a IF formula to pop up a text message if a particular cell is filled by number or text.
What I don't know is how to give a condition on entering anything into the defined cell (numbers or letters) to make the message pop up.
not
=IF(C4= "company ","Name"," ")
I need this defined condition (i.e Company) to be removed & let the formula to accept anything if entered into C4
Please help me
What I don't know is how to give a condition on entering anything into the defined cell (numbers or letters) to make the message pop up.
not
=IF(C4= "company ","Name"," ")
I need this defined condition (i.e Company) to be removed & let the formula to accept anything if entered into C4
Please help me
Similar Excel Tutorials
How to Find and Fix Errors in Complex Formulas in Excel
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to f ...
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to f ...
AND Function  Check if All Arguments are True
The AND function in Excel checks if every argument you put in it evaluates to TRUE. If everything evaluates to TRU ...
The AND function in Excel checks if every argument you put in it evaluates to TRUE. If everything evaluates to TRU ...
Highlight Rows that Meet a Certain Condition in Excel
In this tutorial I am going to cover how to highlight rows that meet a certain condition. To do this I use the Cond ...
In this tutorial I am going to cover how to highlight rows that meet a certain condition. To do this I use the Cond ...
How to Input, Edit, and manage Formulas and Functions in Excel
In this tutorial I am going to introduce how to input, edit and manage excel formulas. To start entering a formula, ...
In this tutorial I am going to introduce how to input, edit and manage excel formulas. To start entering a formula, ...
Helpful Excel Macros
Determine if a Cell Contains a Function in Excel  Great for Conditional Formatting and Validation  UDF
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
PopUp Message Box When a Cell Reaches a Certain Value or Contains Certain Text
 This macro will display a message box in excel when a cell reaches a certain value or contains certain text. This means
 This macro will display a message box in excel when a cell reaches a certain value or contains certain text. This means
Hide Formulas in a Worksheet and Prevent Deletion
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
PopUp Message Box When a Range of Cells Reaches a Certain Average
 This macro will display a message box when the numbers within a range of cells reaches an average of 5. The current mac
 This macro will display a message box when the numbers within a range of cells reaches an average of 5. The current mac
Name Worksheets Based on Cell Contents
 This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
 This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Similar Topics
Hello,
My Cell C3 is a numeric value.....I have set conditional formatting to
make the text red and bold when the number is equal or less than 10,000
is there a way I can make cell E3 display a message when the C3
condition is true?? or if not a message is there a way to make a
message box pop up when my C3 condition of less then or equal to 10,000
is true?
how would I do something like this?
My Cell C3 is a numeric value.....I have set conditional formatting to
make the text red and bold when the number is equal or less than 10,000
is there a way I can make cell E3 display a message when the C3
condition is true?? or if not a message is there a way to make a
message box pop up when my C3 condition of less then or equal to 10,000
is true?
how would I do something like this?
My formula is checking for a certain condition. When the condition is met, I want it to return an error message, so the result cannot be summarized into an aggregate number for that column and I can easily spot it later in a pivot table. The complexity is that I am trying to create an custom message showing "NEED PRICE" as a result of that cell. I am trying to use an IFERROR formula, but since the result is still a text ("NEED TEXT"), the cell simply gets skipped over when being summarized and the summary of that column (Total Sales) still contains a number. How can I make the result of the cell read as an error (so it doesn't tally that column), but still have the custom text "NEED TEXT" in there?
Guys ,
I need some help here regarding to If condition , what i need it is how to make If condition help me to solve an equation (Specific formula ) and this formula will be decide it upon the condition if agree or not and showing the reult in different cell , to make it easer for you this is an example
IF H1 > 0 THEN I NEED TO APPLY A FORMULA OF A1= x+y
if H1
I need some help here regarding to If condition , what i need it is how to make If condition help me to solve an equation (Specific formula ) and this formula will be decide it upon the condition if agree or not and showing the reult in different cell , to make it easer for you this is an example
IF H1 > 0 THEN I NEED TO APPLY A FORMULA OF A1= x+y
if H1
Hi,
I am trying to build a code where some message needs to pop out when a certain condition is met.
lets say the condition is that Cells("B12").Value > 500.
This cell has a formula which is the sum of some other cells. As soon as this sum crosses 500, i need a message to pop up using msgbox.
Note: The if conditions will not work for me it checks the cell value only when it runs. As in, if the value in the cell is already greater than 500 at the time of running the code, then only it will pop up the message. i want the code to be such that when it runs, it should put some condition in the cell so that message pops up if the value exceeds 500 even later. (like how conditional formatting works...its not just an if condition there...whenever the given condition meets, the format changes).
Please help me do this.
I searched a lot and found lots of info about conditional 'formatting' but couldn't work my way around this.
Any help would be appreciated.
Regards,
nonie
I am trying to build a code where some message needs to pop out when a certain condition is met.
lets say the condition is that Cells("B12").Value > 500.
This cell has a formula which is the sum of some other cells. As soon as this sum crosses 500, i need a message to pop up using msgbox.
Note: The if conditions will not work for me it checks the cell value only when it runs. As in, if the value in the cell is already greater than 500 at the time of running the code, then only it will pop up the message. i want the code to be such that when it runs, it should put some condition in the cell so that message pops up if the value exceeds 500 even later. (like how conditional formatting works...its not just an if condition there...whenever the given condition meets, the format changes).
Please help me do this.
I searched a lot and found lots of info about conditional 'formatting' but couldn't work my way around this.
Any help would be appreciated.
Regards,
nonie
Hey guys,
Total newb question but if I have an if/then condition in a cell that references another cell for that condition, is there a way to make the referenced cell condition a text wildcard? As in if(C40=*"John"*, X, Y). And the condition will be true if C40 contains the word John in it along with any other text.
Does this make sense?
J.
Total newb question but if I have an if/then condition in a cell that references another cell for that condition, is there a way to make the referenced cell condition a text wildcard? As in if(C40=*"John"*, X, Y). And the condition will be true if C40 contains the word John in it along with any other text.
Does this make sense?
J.
Hi everyone,
I have a nested IF function I'm having issues with. My workbook is laid out as follows:
Sheet1 contains two dropdown lists, in Cells E2 and F2, and a number field, G2.
The list in E2 may have the following values: "Condition 1" or "Condition 2". The list in F2 may have the following values: "Condition 3" or "Condition 4". Both are text fields.
I need to evaluate for the appearance of the following combinations: ("Condition 1" AND "Condition 3") OR ("Condition 1 AND "Condition 4"), ("Condition 2" AND "Condition 3") OR ("Condition 2 AND "Condition 4").
The results of the conditions use the value of the freeform number field, G2, and manipulate it referencing various values on Sheet3. I'm confident the results portion (the manipulations of G2) are correct, it's the formatting of the IF statement I'm having problems with.
I created the following statement to try to accomplish this:
=IF(AND(E2="Condition 1", F2="Condition 3"), G2*Sheet3!C1, IF(AND(E2="Condition 1", F2="Condition 4"),(G2*Sheet3!C1)*Sheet3!F4, IF(AND(E2="Condition 2", F2="Condition 3")(G2*Sheet3!C1)*Sheet3!F2 IF(AND(E2="Condition 2", F2="Condition 4") (((G2*Sheet3!C1)*Sheet3!F2)*Sheet3!F4), 0))))
However, it doesn't work, Excel tells me "The formula you typed contains an error." I've been searching this site but can't seem to find a readily available answer.
I realize I could be way off, or it could be an easy fix  I'm new to more complex logic in Excel, so I'll thank you in advance for your patience with the newb.
Can anyone give me a correctlyformatted version of that statement, or point me to where I need to look to figure it out myself?
Many thanks,
Rich
I have a nested IF function I'm having issues with. My workbook is laid out as follows:
Sheet1 contains two dropdown lists, in Cells E2 and F2, and a number field, G2.
The list in E2 may have the following values: "Condition 1" or "Condition 2". The list in F2 may have the following values: "Condition 3" or "Condition 4". Both are text fields.
I need to evaluate for the appearance of the following combinations: ("Condition 1" AND "Condition 3") OR ("Condition 1 AND "Condition 4"), ("Condition 2" AND "Condition 3") OR ("Condition 2 AND "Condition 4").
The results of the conditions use the value of the freeform number field, G2, and manipulate it referencing various values on Sheet3. I'm confident the results portion (the manipulations of G2) are correct, it's the formatting of the IF statement I'm having problems with.
I created the following statement to try to accomplish this:
=IF(AND(E2="Condition 1", F2="Condition 3"), G2*Sheet3!C1, IF(AND(E2="Condition 1", F2="Condition 4"),(G2*Sheet3!C1)*Sheet3!F4, IF(AND(E2="Condition 2", F2="Condition 3")(G2*Sheet3!C1)*Sheet3!F2 IF(AND(E2="Condition 2", F2="Condition 4") (((G2*Sheet3!C1)*Sheet3!F2)*Sheet3!F4), 0))))
However, it doesn't work, Excel tells me "The formula you typed contains an error." I've been searching this site but can't seem to find a readily available answer.
I realize I could be way off, or it could be an easy fix  I'm new to more complex logic in Excel, so I'll thank you in advance for your patience with the newb.
Can anyone give me a correctlyformatted version of that statement, or point me to where I need to look to figure it out myself?
Many thanks,
Rich
Is there another formula that works like vlookup but isn't concerned about the source data being in ascending order? I know of one that works when you can look up based on 2 conditions but can't seem to make it work when I only have 1 condition to meet.
My 2 condition formula is a CSE and is: =index(array to lookup(match(1,array of first condition=condition)*(array of 2nd condition=2nd condition))
Can I do something like the above if I only have one condition to meet?
My 2 condition formula is a CSE and is: =index(array to lookup(match(1,array of first condition=condition)*(array of 2nd condition=2nd condition))
Can I do something like the above if I only have one condition to meet?
I am trying to condition format cells that have a formula in them. I would like the condition to be based onthe result of the formula. The first condition I want is if the result is between tomorrow and 3 months from now I would like to make the result written in green. I tried the following to achieve this:
"cell value is","between","TODAY()90","tODAY()1"
The other condition I want is if the result is today or an earlier date, I would like the result to be written in red. I tried this to achieve:
"cell value is","less than or equal to","TODAY()"
Any other result I would like to be left alone. My only possible results will be a date or the test "N/A". But my results seem to only satisfy the second condition because everything was written in red.
"cell value is","between","TODAY()90","tODAY()1"
The other condition I want is if the result is today or an earlier date, I would like the result to be written in red. I tried this to achieve:
"cell value is","less than or equal to","TODAY()"
Any other result I would like to be left alone. My only possible results will be a date or the test "N/A". But my results seem to only satisfy the second condition because everything was written in red.
Hello,
I am trying to conditionally sum numbers over 52 worksheets based upon up to 3 conditions. One condition is a date, the other condition is a company name. Right now I am using only 2 conditions but may add a third.
I am using an array formula to get the result I need for that worksheet. Here it is:
{=SUM((I4:M4=V5)*(F6:F42=V4)*I6:M42)}
My question is this.....How do I get the results I need for all 52 worksheets in one formula?
All information on all worksheets are in the exact same cell references.
My goal is to have a reporting worksheet that has all 365 days of the year and depending on what company is is in the first cell; all dates have the respective number that for that day that is contained on another worksheet.
I know my description is a little vuage, if I need to clarify, please let me know.
I am trying to conditionally sum numbers over 52 worksheets based upon up to 3 conditions. One condition is a date, the other condition is a company name. Right now I am using only 2 conditions but may add a third.
I am using an array formula to get the result I need for that worksheet. Here it is:
{=SUM((I4:M4=V5)*(F6:F42=V4)*I6:M42)}
My question is this.....How do I get the results I need for all 52 worksheets in one formula?
All information on all worksheets are in the exact same cell references.
My goal is to have a reporting worksheet that has all 365 days of the year and depending on what company is is in the first cell; all dates have the respective number that for that day that is contained on another worksheet.
I know my description is a little vuage, if I need to clarify, please let me know.
Code:
I use the above formula down a column in sheet 2 of my worksheet. Basically, it matches the text found in column N of sheet 1 to a corresponding code and inserts that into the cell the formula is in.
However, when I delete the row that the formula references in sheet 1, I get the ref# error in the corresponding cell in sheet 2.
Is there any way to make it more robust so that if the referenced row in sheet 1 is deleted, it will then reference the new row that replaces it?
Many thanks in advance!
=IF('Sheet 1 '!N5="Condition 1",1,"")&IF('Sheet 1 '!N5="Condition 2",2,"")&IF('Sheet 1 '!N5="Condition 3",3,"")
I use the above formula down a column in sheet 2 of my worksheet. Basically, it matches the text found in column N of sheet 1 to a corresponding code and inserts that into the cell the formula is in.
However, when I delete the row that the formula references in sheet 1, I get the ref# error in the corresponding cell in sheet 2.
Is there any way to make it more robust so that if the referenced row in sheet 1 is deleted, it will then reference the new row that replaces it?
Many thanks in advance!
I somehow want to make an error if a certain condition is not met and have a message box pop up to explain the error and then end my macro, but continue if there is no error.
The condition is this: if the number (value) in a particular cell is not equal to the total number of sheets in my workbook then I want the macro to end and display the message: "You need to Press Ctrl+Shift+C."
The value of the particular cell can be found by the following statement:
Sheets("Initial Input").Range("O10").Value
and the total number of sheets should be able to be found with something like this:
Application.Sheets.Count
I was thinking about using an if statement, but I'm open to using any kind of idea.
The condition is this: if the number (value) in a particular cell is not equal to the total number of sheets in my workbook then I want the macro to end and display the message: "You need to Press Ctrl+Shift+C."
The value of the particular cell can be found by the following statement:
Sheets("Initial Input").Range("O10").Value
and the total number of sheets should be able to be found with something like this:
Application.Sheets.Count
I was thinking about using an if statement, but I'm open to using any kind of idea.
Hi friends,
MrExcel forum has been of great help in learning the uses of Sumproduct function as I searched many useful threads on this.
Now I am trying to find out how to apply AND, OR, NOT combination for getting a count/sum.
e.g.
1. Count/sum, If (condition 1) OR (Condition 2) OR (Condition 3) are met
2. Count/sum, If (condition 1) AND ((Condition 2) OR (Condition 3)) are met
3. Count/sum, If ((condition 1) AND ((Condition 2)) OR ((Condition 3) AND (Condition 4)) are met
4. Count/sum, If ((condition 1) OR ((Condition 2)) AND ((Condition 3) OR (Condition 4)) are met
5. Count/sum, If (condition 1) is NOT met
6. Count/sum, If (condition 1) OR (Condition 2) NOT met
7. Count/sum, If (condition 1) AND (Condition 2) NOT met
and similar ones.
Can anyone put light on general arrangements for AND, OR, NOT combination in Sumproduct function. I could not find any thread on this.
Regards,
Uttam
MrExcel forum has been of great help in learning the uses of Sumproduct function as I searched many useful threads on this.
Now I am trying to find out how to apply AND, OR, NOT combination for getting a count/sum.
e.g.
1. Count/sum, If (condition 1) OR (Condition 2) OR (Condition 3) are met
2. Count/sum, If (condition 1) AND ((Condition 2) OR (Condition 3)) are met
3. Count/sum, If ((condition 1) AND ((Condition 2)) OR ((Condition 3) AND (Condition 4)) are met
4. Count/sum, If ((condition 1) OR ((Condition 2)) AND ((Condition 3) OR (Condition 4)) are met
5. Count/sum, If (condition 1) is NOT met
6. Count/sum, If (condition 1) OR (Condition 2) NOT met
7. Count/sum, If (condition 1) AND (Condition 2) NOT met
and similar ones.
Can anyone put light on general arrangements for AND, OR, NOT combination in Sumproduct function. I could not find any thread on this.
Regards,
Uttam
Can anyone help with this please? The formula I have checks for these conditions:
 to see if there is text is cell C221, and if there isn't then the target cell remains blank. (Working).
 if there is text in cell C221, and cell G221 is empty then the target cell should say "No". (Broken! Returns 'FALSE' value instead of "No"...)
 if there is text in cell C221, and cell G221 contains one of the 5 criteria, then the target cell should say "Yes!". (Working).
Here is the formula  how do I fix the broken bit?
=IF((C221=""),"",IF((G221="Condition 1"),"Yes",IF((G221="Condition 2"),"Yes",IF((G221="Condition 3"),"Yes",IF((G221="Condition 4"),"Yes",IF((G221="Condition 5"),"Yes"))))))
Many thanks,
Jeremy.
 to see if there is text is cell C221, and if there isn't then the target cell remains blank. (Working).
 if there is text in cell C221, and cell G221 is empty then the target cell should say "No". (Broken! Returns 'FALSE' value instead of "No"...)
 if there is text in cell C221, and cell G221 contains one of the 5 criteria, then the target cell should say "Yes!". (Working).
Here is the formula  how do I fix the broken bit?
=IF((C221=""),"",IF((G221="Condition 1"),"Yes",IF((G221="Condition 2"),"Yes",IF((G221="Condition 3"),"Yes",IF((G221="Condition 4"),"Yes",IF((G221="Condition 5"),"Yes"))))))
Many thanks,
Jeremy.
Two queries:
I'm trying to do an IF(AND where the first logical is that one cell contains the specified text and the second logical is that another cell in a multiple of 5000 but i'm completely stuck.
If TRUE give me the text "Loan"
If FALSE give me a blank cell
1st condition: Find some (not all) the text within cell S1. The cell will start with "ABC LIMI" but then can contain an random combination of numbers, spaces, *'s or letters.
2nd condition: Cell I1 value is a multiple of 5000 
=IF(AND(S1="ABC LIMI",I1 is a multiple of 5000),"Loan", zero)
Any chance this can be done?
I'm trying to do an IF(AND where the first logical is that one cell contains the specified text and the second logical is that another cell in a multiple of 5000 but i'm completely stuck.
If TRUE give me the text "Loan"
If FALSE give me a blank cell
1st condition: Find some (not all) the text within cell S1. The cell will start with "ABC LIMI" but then can contain an random combination of numbers, spaces, *'s or letters.
2nd condition: Cell I1 value is a multiple of 5000 
=IF(AND(S1="ABC LIMI",I1 is a multiple of 5000),"Loan", zero)
Any chance this can be done?
Hi,
I have several hundreds of different formulas in one column. I need to modify every formula. Each formula should be extended with if statement. This statement is almost identical for all the formulas  condition is always same, the only part that differs is that it should compare the value of the cell that is on the left for the formula. So if I give the address, the row number would differ.
I thought I reach my aim by CONCATENATE function. All I would need is to:
1) take actual formulas as text
2) prepare if condition for each row by dragging
3) CONCATENATE texts
4) Paste a new formula as text into cells that where old formulas were.
Unfortunately I don't know how to copy the text (not values) of my formulas somewhere else
Best,
Michal
I have several hundreds of different formulas in one column. I need to modify every formula. Each formula should be extended with if statement. This statement is almost identical for all the formulas  condition is always same, the only part that differs is that it should compare the value of the cell that is on the left for the formula. So if I give the address, the row number would differ.
I thought I reach my aim by CONCATENATE function. All I would need is to:
1) take actual formulas as text
2) prepare if condition for each row by dragging
3) CONCATENATE texts
4) Paste a new formula as text into cells that where old formulas were.
Unfortunately I don't know how to copy the text (not values) of my formulas somewhere else
Best,
Michal
Hello Everyone,
I have 1700 company names populating the column of a large table. All the company names are text, without any zero or blank entries. There are, however, many duplicate names in this column.
I'm doing some analysis that requires me to calculate the number of unique company names. For reasons that are too complex to explain here, it is not appropriate in this situation to filter out, hide or remove the duplicates names (and the rows of data of which they are a part).
So, I found a lovely little array formula that counts the distinct values in a table column that only contains text entries. In this case, the name of my column is "C7", for column seven. Here's the formula:
{=SUM(
1/COUNTIF(Table[C7],Table[C7])
)}
This formula returns an intelligent answer of 1135 unique company names.
I've also realized that I can add a simple little condition that will exclude some specific entries (though not necessarily all instances of that company name) before the number of distinct names is counted. That formula is:
{=SUM(
IF(Table[C8]="Yes",
1/COUNTIF(Table[C7],Table[C7])
))}
The answer now is an intelligent 630, meaning that 630 unique company names remain in the column after removing those entries whose adjacent cell in column C8 is not "Yes".
Now, here's where things go haywire. If I add a second condition, like this:
{=SUM(
IF(Table[C8]="Yes",
IF(Table[C12]<>0,
1/COUNTIF(Table[C7],Table[C7])
)))}
Suddenly, the answer is a nonsensical 591.9184412 !!!
How can that be? At a minimum, I expect a whole number answer from this formula! What is happening? Have I stumbled on an Excel bug?
I look forward to your workarounds and comments!
Cheers,
Jay
PS: I am, however, looking for a single (array) formula that will go into a single cell.
I have 1700 company names populating the column of a large table. All the company names are text, without any zero or blank entries. There are, however, many duplicate names in this column.
I'm doing some analysis that requires me to calculate the number of unique company names. For reasons that are too complex to explain here, it is not appropriate in this situation to filter out, hide or remove the duplicates names (and the rows of data of which they are a part).
So, I found a lovely little array formula that counts the distinct values in a table column that only contains text entries. In this case, the name of my column is "C7", for column seven. Here's the formula:
{=SUM(
1/COUNTIF(Table[C7],Table[C7])
)}
This formula returns an intelligent answer of 1135 unique company names.
I've also realized that I can add a simple little condition that will exclude some specific entries (though not necessarily all instances of that company name) before the number of distinct names is counted. That formula is:
{=SUM(
IF(Table[C8]="Yes",
1/COUNTIF(Table[C7],Table[C7])
))}
The answer now is an intelligent 630, meaning that 630 unique company names remain in the column after removing those entries whose adjacent cell in column C8 is not "Yes".
Now, here's where things go haywire. If I add a second condition, like this:
{=SUM(
IF(Table[C8]="Yes",
IF(Table[C12]<>0,
1/COUNTIF(Table[C7],Table[C7])
)))}
Suddenly, the answer is a nonsensical 591.9184412 !!!
How can that be? At a minimum, I expect a whole number answer from this formula! What is happening? Have I stumbled on an Excel bug?
I look forward to your workarounds and comments!
Cheers,
Jay
PS: I am, however, looking for a single (array) formula that will go into a single cell.
Hi everybody,
I have a text file named "C:\Text.Txt" with records in the following format:
EventDate Message
31032009,Please Prepare Quartely Reports
15042009,Send Dunning Letters
30062009,Issue Second Qtr Reports
I need a macro that should work when excel starts and read each record in the above text file and display the message given after the EventDate on a message Box if the following condition is met per record:
If the EventDate minus Today's Date is >= 0 and < 60
If multiple records meet the above condition, then those records should be concatenated and be displayed on a single message box rather than many message boxes.
The idea is to pop up a message box when excel starts and displays the events to be done within the next 60 days.This serves as a reminder.
Hope I have explained well.
Thanks
I have a text file named "C:\Text.Txt" with records in the following format:
EventDate Message
31032009,Please Prepare Quartely Reports
15042009,Send Dunning Letters
30062009,Issue Second Qtr Reports
I need a macro that should work when excel starts and read each record in the above text file and display the message given after the EventDate on a message Box if the following condition is met per record:
If the EventDate minus Today's Date is >= 0 and < 60
If multiple records meet the above condition, then those records should be concatenated and be displayed on a single message box rather than many message boxes.
The idea is to pop up a message box when excel starts and displays the events to be done within the next 60 days.This serves as a reminder.
Hope I have explained well.
Thanks
Hello! I need a bit of help, please. I have a running spreadsheet that uses the mod to highlight alternate cells. I've set it up for the entire document under conditional formatting as =MOD(ROW(),2)=0
I have one particular column that contains a sum formula. If the sum=0 I want the text to appear clear or white (or ideally be deleted) so as not to appear distracting as a column of zeros with an occasional value. It just seems to make the true values harder to spot. Anyway, I added a second condition to this column that simply states if cell value=0 then format text as "white".
The rows not higlighted by the first condition work fine and the text is white, but the text in the highlighted rows remains black in this column. I tried alternating the order of the two conditions, but then the second condition cancels out the alternating highlights from the first condition.
any suggestions?
thanks
bj
I have one particular column that contains a sum formula. If the sum=0 I want the text to appear clear or white (or ideally be deleted) so as not to appear distracting as a column of zeros with an occasional value. It just seems to make the true values harder to spot. Anyway, I added a second condition to this column that simply states if cell value=0 then format text as "white".
The rows not higlighted by the first condition work fine and the text is white, but the text in the highlighted rows remains black in this column. I tried alternating the order of the two conditions, but then the second condition cancels out the alternating highlights from the first condition.
any suggestions?
thanks
bj
Hello all,
After a bit of googling and searching these forums, I can't quite find an answer to what seems like a pretty simple problem.
I have a list of around 20 companies that belong to either Group A, or Group B. These companies and their groups are listed in Column A (Company) and Column B (Group) on Worksheet B;
Company Name........Company Group
Name 1...................Group A
Name 2...................Group A
Name 3...................Group B
Name 4...................Group A
Name 5...................Group B
etc..........................etc
On Worksheet A, in Column A, company names (that match company names on Worksheet B) are manually entered. I would like Column B to populate with the company's group as defined on Worksheet B, Column B automatically. This is to speed up data entry, so the person entering each company doesn't have to remember, or look up that company's group (A or B).
In short, if I enter "Name 3" in Column A, I would like Column B to automatically populate with the text "Group B" as defined on Worksheet B.
I'm not quite sure where to begin. Company names will be added and removed from time to time, so ideally I would just update Worksheet B and those changes would be reflected on Worksheet A.
Any help would be greatly appreciated.
Thanks.
I am having trouble with conflicting conditional formatting. I have a list
that I need to have highlighted in one of three colors based on the result of
the formula in column D, and I need to have the text adjusted according to
the result in column G. I have tried having the text formatting as condition
one and shading as condition one, in either case, only the first condition is
applied. (Although the cells that meet only one of the conditions format
properly.)
Here are my conditions:
Condition 1: Formula is =$D23>40 (Shading)
Condition 2: Formula is =$D23>25 (Shading)
Condition 3: Formula is =$D23=1 (Text formatting)
I though of using an AND function, but had trouble getting it to work and I
have 6 potential states and the limit of three will not cover them:
No shade, normal text
No shade, grey ital text
Shade 1, normal text
Shade 1, grey ital text
Shade 2, normal text
Shade 2, grey ital text
Thanks for any help you can offer

L
that I need to have highlighted in one of three colors based on the result of
the formula in column D, and I need to have the text adjusted according to
the result in column G. I have tried having the text formatting as condition
one and shading as condition one, in either case, only the first condition is
applied. (Although the cells that meet only one of the conditions format
properly.)
Here are my conditions:
Condition 1: Formula is =$D23>40 (Shading)
Condition 2: Formula is =$D23>25 (Shading)
Condition 3: Formula is =$D23=1 (Text formatting)
I though of using an AND function, but had trouble getting it to work and I
have 6 potential states and the limit of three will not cover them:
No shade, normal text
No shade, grey ital text
Shade 1, normal text
Shade 1, grey ital text
Shade 2, normal text
Shade 2, grey ital text
Thanks for any help you can offer

L
When I post the following message I can't get all of the condition 1 formula to show up in the post. What do I need to do?
Cells C16:C29 have conditional formatting as follows
First Condition ($C16<AVERAGE($A16:$B16)$C$10
Second Condition ($C16>AVERAGE($A16:$B16)+$C$11
Goldi
Cells C16:C29 have conditional formatting as follows
First Condition ($C16<AVERAGE($A16:$B16)$C$10
Second Condition ($C16>AVERAGE($A16:$B16)+$C$11
Goldi
Looking to input a formula in A1 to check for a condition if B1=E1. If the condition is true, C1 needs to be populated with a text string already inputed into D1. Instead, I just get the (True/False) condition in A1. Sample formula below:
=IF(B1=E1,C1=D1,"")
I understand that the issue lies in the C1=D1 declaration. Help would be greatly appreciated.
Thanks, cashaau
=IF(B1=E1,C1=D1,"")
I understand that the issue lies in the C1=D1 declaration. Help would be greatly appreciated.
Thanks, cashaau
Hi
I have a problem in getting a Pop up message in excel. I dont know whether formula can do the trick or should i take a help of micro.Kindly advise which is preferable and the solution for the same.
The problem is :
I have 2 conditions and if any of the condition comes true , i want a pop up stating that "you have to calculate"( It should serve like a reminder).
Condition 1: In cell B if i insert "CA", and in cell E if i insert "no" and in cell G if i insert "lease".. I want a pop up stating "you have to calculate"
Condition 2: In cell B if i insert "TX" and in cell H if i insert "yes".. I should get a pop up "you have to calculate"
Awaiting a reply.
Thanks
I have a problem in getting a Pop up message in excel. I dont know whether formula can do the trick or should i take a help of micro.Kindly advise which is preferable and the solution for the same.
The problem is :
I have 2 conditions and if any of the condition comes true , i want a pop up stating that "you have to calculate"( It should serve like a reminder).
Condition 1: In cell B if i insert "CA", and in cell E if i insert "no" and in cell G if i insert "lease".. I want a pop up stating "you have to calculate"
Condition 2: In cell B if i insert "TX" and in cell H if i insert "yes".. I should get a pop up "you have to calculate"
Awaiting a reply.
Thanks
Hello, I have a various difficulties within this excel problem.
counting combinations.xlsx
I must calculate all combinations of 7 numbers that give sum of 100. Numbers must be in interval from 1 to 39.
First condition is that sum of numbers must be changeable (i mentioned before sum of 100 to be simpler so I typed number 100 in the document). The sum can be also 150 or 88 for example, depending what number is entered in cell K2.
Second condition is that every combination must be unique (no such things like 7,8,9,10,16,20,30 and 30,20,16,10,9,8,7 and 7,8,10,9,3,20,16). This is example how results shouldn't be because all 3 combinations have same numbers only with different position/order). Only one combination of same numbers is possible and numbers must be ordered from smaller to larger numbers).
Is this possible to do, what is best option: functions or maybe some macro?
I am new to Excel and conditional formatting, but from reading threads and various sites I believe that the conditions have to be in a sort of reverse order.
The users input would be starting from Row 8.
Column A & B would be text and Columns C through to AH would be dates in the format 02Feb07.
I am after trying to do the the following:
Condition 1
If all cells in a row from Column A through to Column AH are not empty then make text white and backcolor black. (Only in column A would be ideal, but not 100% necessary)
Condition 2
If some cells are full and some cells are empty, make the empty cells backcolor Yellow. (Again, it would be nice to make this conditional that the cell in Column A had text in it)
Condition 3
I would like to conditionally format the cells Range(C8:AH1000), but until a row is used, by the user adding input through a form or onto the sheet, I would like to have the cells with no backcolor.
If I could meet this part of Condition 2:
Quote:
it would be nice to make this conditional that the cell in Column A had text in it
then condition 3 would not apply.
At the moment this is what I have for the 3 conditions:
Condition 1
Code:
then there is no backcolor
Condition 2
Code:
then Black backcolor and white text
Condition 3
Code:
empty cells backcolor = Yellow
At the moment this doesn't work, either if the complete row is fully used, or if the cells are empty.
Any pointers in the right direction would be great.
Thanks
The users input would be starting from Row 8.
Column A & B would be text and Columns C through to AH would be dates in the format 02Feb07.
I am after trying to do the the following:
Condition 1
If all cells in a row from Column A through to Column AH are not empty then make text white and backcolor black. (Only in column A would be ideal, but not 100% necessary)
Condition 2
If some cells are full and some cells are empty, make the empty cells backcolor Yellow. (Again, it would be nice to make this conditional that the cell in Column A had text in it)
Condition 3
I would like to conditionally format the cells Range(C8:AH1000), but until a row is used, by the user adding input through a form or onto the sheet, I would like to have the cells with no backcolor.
If I could meet this part of Condition 2:
Quote:
it would be nice to make this conditional that the cell in Column A had text in it
then condition 3 would not apply.
At the moment this is what I have for the 3 conditions:
Condition 1
Code:
=Len(Range(A:AH))=0
then there is no backcolor
Condition 2
Code:
=Len(Range(A:AH))=9
then Black backcolor and white text
Condition 3
Code:
=Len(Range(C:AH))<>9
empty cells backcolor = Yellow
At the moment this doesn't work, either if the complete row is fully used, or if the cells are empty.
Any pointers in the right direction would be great.
Thanks