wrt1mea
asked on
Excel Conditional Formatting
I am using a formula in conditional formatting to look at as cell and determine whether it is true or false.
My true false formula works. It simply looks at previous week and currents week data. If its the same, true, not the same, it's false.
I have verified the cell contents as true, and the conditional formatting formula will insert blue text bold letter. It should only do that when it is FALSE!
Here is the formula that I am using in conditional formatting:
Applies to : =$D$10:$D$150
='Projects'!$BG10=FALSE
The $BG column is where I am looking on another sheet, starting at row 10. There are approximately 130 rows of data.
Do I not have something set right with the formula to look at the range of cells, i.e., BG10:BG130...???
My true false formula works. It simply looks at previous week and currents week data. If its the same, true, not the same, it's false.
I have verified the cell contents as true, and the conditional formatting formula will insert blue text bold letter. It should only do that when it is FALSE!
Here is the formula that I am using in conditional formatting:
Applies to : =$D$10:$D$150
='Projects'!$BG10=FALSE
The $BG column is where I am looking on another sheet, starting at row 10. There are approximately 130 rows of data.
Do I not have something set right with the formula to look at the range of cells, i.e., BG10:BG130...???
There is nothing wrong in your conditional formatting formula.
Select your range where you want to apply conditional formatting i.e. $D$10:$D$150 in this case.
Now Conditional Formatting --> New Rule --> Select "Use a formula to determine which cells to format" --> In the next window, in the formula box type of paste formula ='Projects'!$BG10=FALSE --> Format --> Set format as per your choice. --> OK
Just pay attention that the first row reference in your formula and the first cell in the selected range should have identical row reference i.e. row 10. So conditional formatting rule will look at BG10 to format D10 and BG11 to format D11 and so on.
If this doesn't help, please upload a sample workbook to let us know the exact problem.
Select your range where you want to apply conditional formatting i.e. $D$10:$D$150 in this case.
Now Conditional Formatting --> New Rule --> Select "Use a formula to determine which cells to format" --> In the next window, in the formula box type of paste formula ='Projects'!$BG10=FALSE --> Format --> Set format as per your choice. --> OK
Just pay attention that the first row reference in your formula and the first cell in the selected range should have identical row reference i.e. row 10. So conditional formatting rule will look at BG10 to format D10 and BG11 to format D11 and so on.
If this doesn't help, please upload a sample workbook to let us know the exact problem.
Or you may try to use the following formula for conditional formatting to see if that makes difference...
=AND(Projects!$BG10<>"",Pr
=AND(Projects!$BG10<>"",Pr
ASKER
OK....I have verified the first row in the formula and the first cell in the row reference start on row 10.
My true false formula is of the simplest version: =d10=at10, returns true or false.
I can easily verify the true false results on the other sheet.
The conditional formatting formula still isn't working correctly.
I will try to fig out a way to upload an example, but its going to be very hard obfuscate the data from a production environment.
My true false formula is of the simplest version: =d10=at10, returns true or false.
I can easily verify the true false results on the other sheet.
The conditional formatting formula still isn't working correctly.
I will try to fig out a way to upload an example, but its going to be very hard obfuscate the data from a production environment.
ASKER
JEaston,
I tried your formula and am experiencing the same result...
I tried your formula and am experiencing the same result...
Do you want to conditional format the range $D$10:$D$150 if it contains False as a result of the formula =AT10 in D10 which is copied down to D150?
If so, what is the role of BG10 here?
If so, what is the role of BG10 here?
ASKER
OK, sorry for the confusion.
On the project sheet in my workbook, in BG10:BG150 it returns true false when it looks at D10 and AT10. D11 and AT11, etc.
On the project sheet in my workbook, in BG10:BG150 it returns true false when it looks at D10 and AT10. D11 and AT11, etc.
ASKER
On the project sheet, D10 is current week info. AT is previous week info. If they are the same, it returns true and thus no need for conditional formatting.
If they are not the same, it returns false, and I need conditional formatting to work. Its simply looks at last week's dates and compares. If it is false, I want my conditional formatting returns a blue bold date....just an FYI
If they are not the same, it returns false, and I need conditional formatting to work. Its simply looks at last week's dates and compares. If it is false, I want my conditional formatting returns a blue bold date....just an FYI
If you want to conditional format the range BG10:BG150, you can use simply =AND($BG10<>"",$BG10=FALSE
And if you want to conditional format the range D10:D150, you can simple use =AND($D10<>"",$D10=FALSE) as a formula for conditional formatting.
Add the sheet reference with the formula if the conditional formatting range and formula referenced cell is on different sheet.
If that doesn't help, please make a dummy workbook just to have max 10 rows with some dummy data and upload it here along with the description about which range you want to conditionally format and based on which cells.
And if you want to conditional format the range D10:D150, you can simple use =AND($D10<>"",$D10=FALSE) as a formula for conditional formatting.
Add the sheet reference with the formula if the conditional formatting range and formula referenced cell is on different sheet.
If that doesn't help, please make a dummy workbook just to have max 10 rows with some dummy data and upload it here along with the description about which range you want to conditionally format and based on which cells.
ASKER
OK....I will work on getting something submitted this afternoon.
I am referencing the projects sheet where most of the heavy lifting via formulas are. In that sheet is where it looks at D10 for the current week and AT10 for the previous weeks data. I do this to compare the date information between the two weeks. If they are the same (true), no conditional formatting is needed on my main report. If they are not the same (false), I need the conditional formatting to work.
I want to conditional format the range D10:D150 on my main report. From my main report, I want to look at the project sheet for True / False in BG10. On the project sheet, it's looking at its OWN D10, then looks at its own AT10. Within the project sheet, Column BG compares the two dates and returns true or false.
I want the main sheet to look at the project sheet BG10 for true false and apply the conditional formatting appropriately.
Thank you for your consistent willingness to help.
I am referencing the projects sheet where most of the heavy lifting via formulas are. In that sheet is where it looks at D10 for the current week and AT10 for the previous weeks data. I do this to compare the date information between the two weeks. If they are the same (true), no conditional formatting is needed on my main report. If they are not the same (false), I need the conditional formatting to work.
I want to conditional format the range D10:D150 on my main report. From my main report, I want to look at the project sheet for True / False in BG10. On the project sheet, it's looking at its OWN D10, then looks at its own AT10. Within the project sheet, Column BG compares the two dates and returns true or false.
I want the main sheet to look at the project sheet BG10 for true false and apply the conditional formatting appropriately.
Thank you for your consistent willingness to help.
Okay. Based on your description, I have made a dummy workbook and tried to place the formula as per the description and applied the conditional formatting in the range D10:D150 on Main Sheet based on FALSE returned in the range BG10:BG150 on Projects Sheet.
Does your actual workbook has similar setup, of course the formulas may be different as I am not aware of the actual formula used on the Main Sheet.
I have not used any formula in the range D10:D15 on Main sheet, but it hardly makes any difference as the conditional formatting is dependent upon the Projects!BG10:BG150.
This is the same formula that I suggested in my first post. Is it not working as same as it is working in the example workbook?
If you have difficulty in making a dummy book, just make the proper changes in the same workbook and upload it here again to show what's not working as per your expectations.
Conditional-Formatting.xlsx
Does your actual workbook has similar setup, of course the formulas may be different as I am not aware of the actual formula used on the Main Sheet.
I have not used any formula in the range D10:D15 on Main sheet, but it hardly makes any difference as the conditional formatting is dependent upon the Projects!BG10:BG150.
This is the same formula that I suggested in my first post. Is it not working as same as it is working in the example workbook?
If you have difficulty in making a dummy book, just make the proper changes in the same workbook and upload it here again to show what's not working as per your expectations.
Conditional-Formatting.xlsx
ASKER
Please the attached....
For the problem, look at the projects tab, line AT100 and AT111. It returns True in BG100 and BG111.
Now select the main tab. Look at D78 and D82. They show blue bold. They should not.
As I am trying to think through this...I apply a filter and sort that filter on The Main tab for my report, so that's why you may see the funky reference D78 and D82...I have a feeling this is whats causing the problem...???
CONDITIONAL-FORMATTING-7-30-15.xlsx
For the problem, look at the projects tab, line AT100 and AT111. It returns True in BG100 and BG111.
Now select the main tab. Look at D78 and D82. They show blue bold. They should not.
As I am trying to think through this...I apply a filter and sort that filter on The Main tab for my report, so that's why you may see the funky reference D78 and D82...I have a feeling this is whats causing the problem...???
CONDITIONAL-FORMATTING-7-30-15.xlsx
This is working perfectly as per the conditional formatting formula.
Clear the filters from the col. AT and then you will see the BG78 and BG82 have False in them so D78 and D82 on the Main tab should have the bold and blue font as per your format set.
Where is the confusion then?
If your conditional formatting formula is correct, you can rely on the conditional formatting result blindly no matter if you apply a filter on a column or not.
Best way to check this manually is to place the conditional formula in C10 and copy it down, so a cell in col. D should be conditionally formatted if the corresponding cell in col. C is returning True, as you might be aware that the conditional formatting works on the basis of True or False returned by the conditional formatting formula.
Does this resolves your issue?
Clear the filters from the col. AT and then you will see the BG78 and BG82 have False in them so D78 and D82 on the Main tab should have the bold and blue font as per your format set.
Where is the confusion then?
If your conditional formatting formula is correct, you can rely on the conditional formatting result blindly no matter if you apply a filter on a column or not.
Best way to check this manually is to place the conditional formula in C10 and copy it down, so a cell in col. D should be conditionally formatted if the corresponding cell in col. C is returning True, as you might be aware that the conditional formatting works on the basis of True or False returned by the conditional formatting formula.
Does this resolves your issue?
ASKER
OK...
Main Tab. D78 and D82 show blue bold. They should not.....
Having to clear the production data so I wont publish confidential info, D78 refers to the projects tab AT111, which shows TRUE. Since it shows true, It needs to not have conditional formatting.
D82 refers to AT100. That also shows true. It should not have conditional formatting applied either, but they both do.
Main Tab. D78 and D82 show blue bold. They should not.....
Having to clear the production data so I wont publish confidential info, D78 refers to the projects tab AT111, which shows TRUE. Since it shows true, It needs to not have conditional formatting.
D82 refers to AT100. That also shows true. It should not have conditional formatting applied either, but they both do.
I see.
Then use the following formula for conditional formatting for the range D10:D150.
=PROJECTS!D43<>PROJECTS!AT
And see if you get the desired formatting in the range D10:D150 on the Main Tab.
Then use the following formula for conditional formatting for the range D10:D150.
=PROJECTS!D43<>PROJECTS!AT
And see if you get the desired formatting in the range D10:D150 on the Main Tab.
ASKER
Unfortunately not....that turns other dates that should not be blue, blue.
ASKER
It almost seems as though I need to index / match data to get the result. In the D column, in its list of dates, some will be blue and some will not based of the true false.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK....I think we are on to something here. I tested out first column and I think it works. I will verify and finish testing tomorrow in the morning and follow up with you.
Thank you for your persistence and willingness to help.
Thank you for your persistence and willingness to help.
ASKER
OK...
I think I am figuring out a pattern. I can verify the true false statement. And sometimes, a true statement is coming back with conditional formatting, which it should not be doing.
Once I have all of my info gathered and I present in on the main report, I am filtering out projects that I don't need to show and sorting the filtered results. When I sort the filtered results, it screws up the conditional formatting. If I leave everything alone, it works fine.
So the million dollar question is how to I maintain my conditional formatting statement will sorted, filtered results?
I think I am figuring out a pattern. I can verify the true false statement. And sometimes, a true statement is coming back with conditional formatting, which it should not be doing.
Once I have all of my info gathered and I present in on the main report, I am filtering out projects that I don't need to show and sorting the filtered results. When I sort the filtered results, it screws up the conditional formatting. If I leave everything alone, it works fine.
So the million dollar question is how to I maintain my conditional formatting statement will sorted, filtered results?
ASKER
Thanks for the help!
Open in new window
I.e. conditional formatting is applied with the result is True.Finally, also check it the field is FALSE, or "False". I.e. is it returning text with the word False, or a binary True / False.
Hope this helps.