[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Conditional Formatting - Update to question

Posted on 2011-04-25
8
Medium Priority
?
231 Views
Last Modified: 2012-05-11
I had some help with conditional formatting but i still seem to have an error that i hadn’t been aware of.
Please see attached sheet which shows the error.

Also can someone explain what the original formatting formatting actually refers to......and any changes required to make it work 100%
Looking at the format you used can i just make sure i understand what it means please:

=AND(H5="",G5>TODAY(),G5>0)
I take it =AND means that all the terms in the bracket are checked so:

H5=""  If H5 is blank  AND ALSO
G5>TODAY()  the date in cell G5 is bigger than today’s date AND ALSO
G5>0 the date in G5 is bigger than 0.....not quite sure why this one is included?

THEN Format the cell as shown.
So i think that’s all ok but i am not sure about the G5>0 bit

The other thing i don’t understand is that if i look at the formatting of say cell G20, it still uses
=AND(H5="",G5>TODAY(),G5>0)

Thanks

Copy-of-Format.xlsx
0
Comment
Question by:GordonMasson
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 8

Expert Comment

by:ConUladh
ID: 35458852
The background of that cell (G6) is set to red by default so even though the conditional formatting doesn't apply its red anyway, set it no fill and the rules will work.

The G5>0 is probably just to make sure that text isn't entered.

The reason the rule looks the same for G20 is that it applies to a range, you can edit this rang in the manage rules window.
0
 

Author Comment

by:GordonMasson
ID: 35458886
Ah ... stupid of me, i missed that!

And is this bit?

Looking at the format you used can i just make sure i understand what it means please:

=AND(H5="",G5>TODAY(),G5>0)
I take it =AND means that all the terms in the bracket are checked so:

H5=""  If H5 is blank  AND ALSO
G5>TODAY()  the date in cell G5 is bigger than today’s date AND ALSO
G5>0 the date in G5 is bigger than 0.....not quite sure why this one is included?

THEN Format the cell as shown.
So i think that’s all ok but i am not sure about the G5>0 bit

The other thing i don’t understand is that if i look at the formatting of say cell G20, it still uses
=AND(H5="",G5>TODAY(),G5>0)
0
 
LVL 8

Accepted Solution

by:
ConUladh earned 1800 total points
ID: 35458937
=AND(H5="",G5>TODAY(),G5>0)
I take it =AND means that all the terms in the bracket are checked so:

H5=""  If H5 is blank  AND ALSO
G5>TODAY()  the date in cell G5 is bigger than today’s date AND ALSO
G5>0 the date in G5 is bigger than 0.....not quite sure why this one is included?

Correct


THEN Format the cell as shown.
So i think that’s all ok but i am not sure about the G5>0 bit

To stop a non-valid text entry confusing the matter.

The other thing i don’t understand is that if i look at the formatting of say cell G20, it still uses
=AND(H5="",G5>TODAY(),G5>0)

When you go to Conditional Formatting -> Manage rules you will see that formatting applies to a range so the references in the rule are to the first entry in that range, you can modify the range in that screen as well.


0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35458938
The G5>0 is because cell G5 may or may not be populated based on the sample data he gave me, and he asked that it go through about 600 rows, i applied it from G5:G600 and it only formats the cells that have data......

Brandon
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 200 total points
ID: 35458953
There isn't much point in having both G5>TODAY() and G5>0, if the first is satisfied then the second must be, so it's redundant here - if it's possible that G5 could be text and you don't want to trigger the conditional formatting in that case then you need to check explicitly that G5 is a number (i.e. a date) so in that case you might want to use:

=AND(H5="",G5>TODAY(),ISNUMBER(G5))

In Excel 2007 the condition doesn't explicitly show the change whichever cell you look at (unlike Excel 2003 where the condition would look different, so you'd have =AND(H20="",G20>TODAY(),G20>0) in G20). It work's the same, though, you just have to "imagine" what the condition will be for that cell/row!, as long as you don't use $ signs then the condition will adjust row by row

regards, barry
0
 

Author Comment

by:GordonMasson
ID: 35459264
Thanks guys...

Thats me happy.
Not sure about the points...i never am when there are multiple answers.

ConUladh 400
KnutsonBM 50
barryhoudini 50

Does that seem fair?
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35459390
you can leave me out, i was just adding explanation to the answer i gave before, no worries there. :)

Brandon
0
 

Author Closing Comment

by:GordonMasson
ID: 35459400
Thanks for your help guys
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question