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...???
LVL 1
wrt1meaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John EastonDirectorCommented:
I haven't used conditional formatting for a while, but I always used to use the IF formula.  I don't know if it was needed, but always worked for me.  For you this would be:
=IF('Projects'!$BG10=FALSE,True,False)

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.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Or you may try to use the following formula for conditional formatting to see if that makes difference...

=AND(Projects!$BG10<>"",Projects!$BG10=FALSE)
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

wrt1meaAuthor Commented:
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.
0
wrt1meaAuthor Commented:
JEaston,

I tried your formula and am experiencing the same result...
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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?
0
wrt1meaAuthor Commented:
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.
0
wrt1meaAuthor Commented:
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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If you want to conditional format the range BG10:BG150, you can use simply =AND($BG10<>"",$BG10=FALSE) as a formula for conditional formatting.

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.
1
wrt1meaAuthor Commented:
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.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
1
wrt1meaAuthor Commented:
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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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?
1
wrt1meaAuthor Commented:
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.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I see.
Then use the following formula for conditional formatting for the range D10:D150.

=PROJECTS!D43<>PROJECTS!AT43

And see if you get the desired formatting in the range D10:D150 on the Main Tab.
0
wrt1meaAuthor Commented:
Unfortunately not....that turns other dates that should not be blue, blue.
0
wrt1meaAuthor Commented:
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.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Have you noticed your formula in col. BG on Projects Sheet.
As per the current formula in BG13 = D13=AT13, you get False in BG13 while D13 and AT13 are blank cells though they contains some hidden characters within them.
This is the case with most of your BG Cells.

Do you know this?

If not, change the formula in col. BG on Project sheet like this.....

On Project Sheet, in BG10
=CLEAN(D10)=CLEAN(AT10)
and copy down to row 150.

Now on the Main Sheet for conditional formatting the range D10:D150, use the following formula.

Before applying a new conditional formatting formula for the same range D10:D150, delete the old conditional formatting formula by manage rule and then find the correct formula and delete it.
Then apply the new conditional formatting using the below formula.....

=AND(Projects!B10<>"",Projects!B10=FALSE)

and see what you get this time.

But before applying this formula, notice that you have not symmetrical formula in col. D of main sheet.

D10 =IFERROR((PROJECTS!D70),"")

D11=IFERROR((PROJECTS!D71),"")

D12 =IFERROR((PROJECTS!D12),"") and so on. See if this is correct.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wrt1meaAuthor Commented:
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.
0
wrt1meaAuthor Commented:
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?
0
wrt1meaAuthor Commented:
Thanks for the help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.