Excel template help

[ewornibor]

Hi everyone, I have a friend that is trying to use an Excel template to track a project he's working on. He does NOT have access to Microsoft Project, so he's having to use Excel instead.

He's trying to show the overdue time period on the Calendar and in RED in the following Excel spreadsheet. Does anyone know how to do this automatically, if there is a way...

http://files.engineering.com/getfil...e=Copy_of_Doctrine_Tracker_(Gantt_Chart).xlsx

Thank you!

 

[SkipVought]

Hi,

I really dislike these, "I have this friend..." because many times the assumed approach is suspect or flawed, and so how can we broker a good solution in such an instance? Or how can we ask questions about design intent or regarding the basic specification?

She/he ought to be the one asking the question, not you! And why isn't he/she? Do you have more interest or initiative than she/he?

So I'm done venting. But I'd really like an answer to the last two questions.

What I also need is an example of how this data is to be displayed or at least a detailed explanation of the proposed outcome. Given this data, here's the desired outcome.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ewornibor]

He's not tech savvy so I am asking on his behalf... trying to help him out. Yes, obviously I have more initiative to get the problem solved.

If the value in the ACTUAL END column is greater than the Projected End (which then the value in the days overdue column would be a negative value) - he wants the dates in the calendar... that would be all of the columns to the right of the tasks (column M on...), to be filled in in red those dates where the project is over the projected end date. For example... in line 12 on worksheet tab hb 3-12.1, the actual end date of the task is 14 days beyond the projected end date, so he wants from the Projected end date (in this case 4/28/16) to the Actual end (in this case 5/13/16) to be colored in red on the calendar columns (starting with column m... going right).

I hope that I explained it in enough detail... please let me know and I will elaborate further if necessary.

 

[SkipVought]

Need some major explanation of the structure of this sheet. For instance, I see M10 which is a MERGED RANGE. So in what row can I find can I find 4/28/16; individual dates for each day? Is it Row 9? I'd be guessing.

Please also explain the Conditional Formatting expression that is already there for the blue shade and black? I don't want to guess.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ewornibor]

This is apparently a spreadsheet that he is trying to modify for his purposes. If you scroll down to row 10, where you can see the merged cells that show the dates, 4/28 would be 3 cells from the left where 25 April starts.

You can remove the conditional formatting, again that was a residual from the previous spreadsheet and he's trying to rework it.

 

[SkipVought]

Well then we need a spreadsheet that really is the one that has all the proper structure, formulas, formats. Don't need a work in some other process.

Merged ranges: Here's one of those flawed design decisions. You can simply say, "4/28 would be 3 cells from the left where 25 April starts" (as if an intelligent person could not figure that out) Do you realize what that does to a formula needing a date in a calculation? Merged cells look pretty, but they work counter to code simplicity.

BTW, He/she is already using Conditional Formatting in this range. Why not a Conditional Format for this RED range?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ewornibor]

Ok, can stop working on this, obviously this isn't going to be an easy fix... thanks anyway

 

[SkipVought]

Okay it seems that row 9 are the day by day dates. can you verify that with your friend? That what is in the CF criteria expressions for the Blue/Black CF ranges.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

So if we isolate on row 12, then an additional RED CF cells would be...
[tt]
=OR(AND($E12<=M$9,M$9<=$F12),AND($E12<=M$9,ISBLANK($F12)))
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Gavona]

btw, in case anyone comes back to this post. As Skip said merged cells are often problematic. I often find that Horizontal format of Centre Across Selection does the same job without causing so many issues.

Gavin