AutoPhase Values

[awalsh80]

I am trying to automatically spread (phase) values linearly through a specefic date range.

Cell A1 contains the value (100);
Cell B1 contains the Start Date (Jul 11)
Cell C1 contains the finish date (Oct 11)
Cells D1 thru O1 will be where the formula is entered and will be the date range. (Jan 11 - Dec 11)

I would expect to see the following:

Jan11 Feb11 Mar11 Apr11 May11 Jun11 Jul11 Aug11 Sep11 Oct11
0 0 0 0 0 0 25 25 25 25

Is this possible??

Thanks.

 

[awalsh80]

I was able to get it to work for the first activity, but when I try to copy down or use the format painter on the rest of the activities it doesn't work.

 

[iliace]

You didn't mention other activities in the first question, can you elaborate? Are they in A3-E3, A4-E4, etc?

 

[awalsh80]

It would make more sense if you could see the file I'm working on. I will try to explain better:

Cell A2 =Activity 1
Cell A3 =Activity 2
etc

Cell B2 =Start Date for Activity 1
Cell C2 =Range Start date for Activity 1
Cell D2 =Range Finish date for Activity 1
Cell E2 =Finish Date for Activity 1

Cells G1 - BQ1 =The Weekly Range at the top of the sheet (7/22/11-8/13/12)

I need the CF inside the range starting on G2 through BQ135.

Hope this makes better sense. I can send you the file if you would like to see it.

 

[iliace]

So then, you want the dates B3, C3, D3, E3 to be highlighted in the range G2-BQ2? If that's the case, just take out the $ sign in the conditional formats from the first row, like so

C1=$B$6 becomes C1=$B6

or, in your example, if you have for Rule 1

G1=$B$2 becomes G1=$B2

then you'll be able to use format painter to propagate that rule.

 

[awalsh80]

Also, Is it possible to make the green and red formatting not = to but between the range? The range at the top is week ending dates, but the start/stop dates might not equal those exact dates.

In example, the start date of an activity could be 8/13/11 but there is not 8/13/11 at the top, there is an 8/19/11 that I would want to be green.

Hope that makes sense.

 

[iliace]

You basically need to find the first date that's greater than or equal to your end date, so same principle as we did in the first few examples:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			7/22/11	7/23/11	7/24/11	7/25/11	7/26/11	7/27/11	7/28/11	7/29/11	7/30/11	7/31/11	8/5/11	8/15/11
2	Activity 1	7/23/11	7/25/11	7/29/11	8/1/11
3	Activity 2
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	1. / Formula is =(C1=INDEX($C$1:$N$1, MATCH(TRUE, $C1:$N1>=$E2, 0)))	Abc
C1	2. / Formula is =AND(C1>=$C$2, C1<=$D$2)	Abc
C1	3. / Formula is =C1=$B$2	Abc

 

[awalsh80]

Thanks! I love this site!