• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

conditional formatting -

W

wannabe_guru

New Member
is there anyway to highlight values in a range of cells, based on start dates of a project?


for example:


Start Date 01/01/12


Project A: has dollars budgeted on it for 02/15/12, then 03/15/12, and 04/15/12.

What I would want to do is highlight the cells that have those dollars on them if they are passed that date.
 
Wannabe_guru


Select the cells you want to apply the format to,

Remember the Cell Address of the Top Left corner I'll assume D5 for this example

A1 contains the reference date


Goto Conditional formatting

New Rule

use a Formula

Use the formula =D5>$A$1

Apply a Format

Apply


You may want to read:


http://chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics/

&

http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/
 
[pre]
Code: 
Project A	Start		1-Jan	1-Feb	1-Mar	1-Apr
1111	        1/15/2012       $10.00  $10.00  $10.00  $10.00
[/pre]

What should happe, is that the contents in feb, march, and april should be highlighted.

does this example help?
 
i think i figured it out!


=IF(SUMPRODUCT((M$9:Z$9>$E11)*(M11:Z11))>0,TRUE,FALSE) i can use this formula in my conditional formatting! :D
 
Wannabe_guru


That looks way too complicated for what I think you require


Please add Row and Column headers when you paste date like:

[pre]
Code: 
I	J	K	L	M	N	O	P
2	Project A	Start	1-Jan	1-Feb	1-Mar	1-Apr
3	1111	1/15/2012	$10.00	$10.00	$10.00	$10.00
[/pre]
It makes it so much easier to assist
 
[pre]
Code: 
A               B                  C       D       E      F
Project A	Start		1-Jan	1-Feb	1-Mar	1-Apr
1111	        1/15/2012       $10.00  $10.00  $10.00  $10.00
[/pre]

my forumla should be generated to say, based on the start date in column b, if any cells within the range after the start date has dollars on it, highlight that amount.
 
Select C2:F2 or whatever your last column is

Goto Conditional formatting

New Rule

use a Formula

Use the formula =C$1>$B2

Apply a Format

Apply


Make sure all your Dates are Dates, Not Text
 
[pre]
Code: 
A               B                  C       D       E      F
Project #	Start		1/1/12	2/1/12	3/1/12	4/1/12
1111	        1/15/2012       $10.00  $10.00  $10.00  $10.00
2222            2/9/2012        $20.00  $20.00  $20.00  $20.00
[/pre]

i cant understand, this "whatever" talk you keep mentioning.

why is this so difficult to write a formula that says, ONLY HIGHLIGHT THE VALUES based of the start date that are being budgeted for in other months. it's so frustrating.


by the way your forumla won't work, because it needs some kind of if statement i would think. each of the dates, represent a month, so the condition is if there is DOLLARS past 1/15/12 HIGHLIGHT THOSE VALUES IN THE ROW.
 
Hi ,


Try this CF formula for C2 :


=AND(C$1>$B2,C2>0)


and select the format to be applied.


Copy this to the other cells in row 2 and 3.


Narayan
 
Wannabe_guru

You have posted monthly data that goes from C1 to F2

I said "Select C2:F2 or whatever your last column is"

I am assuming you have or may have in the future more data than just 4 months

So if you have more than 4 months data extending out to Z2

you would select C2:Z2, Z being "the Whatever" the last column of your data is
 
You must log in or register to reply here.
Back
Top