Excel 2007 - Assistance with Conditional Formatting 1

[ladyck3]

I will try to be as brief as possible. I have a master spreadsheet that is linked to individual workbooks one per product. Each product workbook has 4 tabs (one for each department and with their tasks listed which, when complete total 100%)

The Master turns 100% when all 4 tabs total 100% each but the problem is... on the master is a DUE DATE. Eventually all of the product's worksheets will total 100% completion but the problem is, they want to have a column on the Master that denotes whether the 100% was attained on time or was completed after the fact.

I am not a VBA expert.. I an open the editor and save something that someone else writes but if it breaks, I have no clue how to fix it.

With that said, is there a way to accomplish this conditional formatting or having a new column actually somehow show the date when the product workbook is completed without manually having to keep track of 4 different departments and their completion times?

I hope I made sense to everyone and thank you in advance....

LadyCK3
aka: Laurie

 

[SkipVought]

Laurie,

What a mess, having all your source data in different workbooks and sheets. First thing I'd do is CONSOLIDATE. Then you would be able to use Excel's data analysis and reporting feature to their full extent. As it is now, you have to limp along, kluging together data from all these different sources. YUK!!!

With that said, is there a way to accomplish this conditional formatting or having a new column actually somehow show the date when the product workbook is completed without manually having to keep track of 4 different departments and their completion times?

I would hope so. Almost EVERYTHING in a well designed workbook, ought to update 'automatically' with a calculation.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ladyck3]

HA HA HA... not my configuration, trust me. This is "their" work and I'm trying to hobble it.....

I agree, its darn near impossible... it has been passed to me twice now and then handed off to another. She has been trying if/then statements but the now/today does not become static and therein lies the problem....

The master does have a column that does turn 100% when all entities are completed but the problem is documenting the DATE that this information turns to 100%

Skip, trust me I know its impossible....

Thanks for taking a look at my post, however.



LadyCK3
aka: Laurie

 

[SkipVought]

Probably impossible to do with formulas (=Today() or Now())

But can probably do with event driven VBA code, comparing to Date or Now, resulting in a STATIC date/time value.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Kurupt55]

I have a solution
In B1 use this formula
=IF(A1=1,NOW(),0)
where A1 is the % complete
In C1 use this formula
=IF(B1=0,b1,c1)
when A1 reaches 100% C1 will become static with the date and time if required

 

[Kurupt55]

sorry made a mistake with the second formula
should be
=IF(C1=0,B1,C1)

 

[Kurupt55]

Oh and you will need to turn on iteration on for this to work as it is a circular reference

 

[AnotherHiggins]

That's a clever solution, Kurupt, but I wouldn't be comfortable relying on it to store historical data.

If anyone ever comes along and disables iterative calculations you could lose your historical data. IMHO it would be much safer to do as Skip suggests and use a simple macro to replace the formula with the value it returns.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Kurupt55]

good point, i think i just wanted to prove skip wrong about this statement.

Probably impossible to do with formulas (=Today() or Now())

Was quite proud of myself to be honest.

Impossible is Nothing

 

[ladyck3]

This is all under the same vein so I am continuing the thead. This massive deal is now coming under my umbrella and I'm going to be working on changing / consolidating information... as Skip said, into one workbook (GO ME... SMILE) well up to a point anyway. I am no excel expert (but they think I am ) and I don't know how to write VBA scripting....I can plop code into the editor but to write it or edit it or tweak it... I'm lost.

Here's what I envision... there will be different time periods T-6 weeks T-5 Weeks etc In each weekly section there will be different tasks depending on the individual or team.

T -6 Weeks
===========================
Team 1
===========================
Get device 100%
Create Core Specs 75%
Create FAQs 25%
-----------------------
Team 1 Current Score 67%
===========================
Team 2
===========================
Update inventory 100%
Ship Samples 100%
---------------------------
Team 2 Current Score 100% 12/07/2009
===========================
T -5 Weeks (and so it goes)

Ok so in the example above, it shows just 2 teams because at T-6 weeks only 2 of 5 teams are involved, as launch gets closer more teams will be added in the week increment.

What I am seeking is this... at this point, I am utilizing Column A and B.... is there a way, in Column C to have it hard code the date when the scoring cell reaches 100%?

From there I will conditionally format it to correspond with the specific date of launch in order to determine at a glance if the 100% was attained prior to, at, or after its targeted deadline.

Any takers on this? an If Then statement possibly? I can dabble with that.... <shrug> Any suggestions? (this is not a homework assignment (I read the suggested article FAQ181-2886 before continuing this post.)

Thanks so much....
Laurie

LadyCK3
aka: Laurie

 

[SkipVought]

Laurie,

What does your example do to clarify what you need to happen?

Is the ONLY thing you need, a hard date?

If so, your choices are the FORMULA method submitted above by Kurupt55, disproving my previously made assertion, BUT depending on Calculation Iterations being on, OR VBA.

If VBA, please post in forum707.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ladyck3]

Yes, bottom line is the hard coding of a date.

Oh I was not sure about this "Calculation Iterations" thing.... and it sounded like you thought it a good idea but aprehensive about possible corruption.

I don't think I want to get into VBA because I don't know it to fix it if it breaks....

I'll search google on the Calculation Iterations.. or the help file... I have never heard of this....

I will test with this and award stars when I get it to work

Give me a day or two....


LadyCK3
aka: Laurie

 

[ladyck3]

OOPS A key piece of information....
The Launch Date will be in Cell B3

I have not had time to focus on how this formula is to work, but does this affect the calculation? It will be asking of a percentage in cell B10 has reached 100%, if it does, return a hard coded date, if not, the cell would be empty.

I'm doing several different tasks right now and as I try to formulate a demo for the team, I am coming up with scenarios I failed to either realize or explain.

Thank you for your patience.


LadyCK3
aka: Laurie

 

[SkipVought]

I personnaly would NOT hang my hat on iterations.

So it's up to you. What direction do you want to take?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Kurupt55]

I am with skip on this one. the solution i posted works for me on my spreadsheets but with multiple users editing it a simple piece of VBA code will do the same thing but more reliable

Impossible is Nothing

 

[ladyck3]

Since I have no clue on the iterations thing... I just need to have something that is stable and cannot be corrupted by non-excel proficient folks... of which there are many.

Idiot proof basically....

I have to sit and 'look' at the formula.. I've been crunching to complete a different report and its now down so tonight I can focus on the resolution.

If you know of a more foolproof method please let me know.

I do appreciate the continued interest!

LadyCK3
aka: Laurie

 

[lionelhill]

Could you get the people who complete the jobs to enter the date on which they were completed, rather than merely ticking them off as "done"? If so, you could use the datevalue function to convert their text (hopefully!) into an excel numerical date, then find the maximum date on all tasks, and use this to establish when the last task was completed.

Of course people can cheat by entering the wrong date, but they can also cheat by pretending to have completed a job that isn't complete.

And you might be able to turn on some validation to ensure they type a date that is really a date, and equal to today.

 

[ladyck3]

thanks Lionel, trust is not an issue but that is beside the point, this is to be an automatic thing.... there are many steps to the progress chart at hand and when all tasks are completed, the score will be color coded and I need the 'finish date' to plant itself.

Kurupt's option does work...

Skip, I still have no clue what "Calculation Iterations" is all about I have been catching up on things at work and have not had time to even look to see what you are referring to but I should be able to in the morning.

Thanks so much... like I said, the formulas do work and I am very grateful!!!



LadyCK3
aka: Laurie

 

[lionelhill]

ladyck3,

the issue with "calculation iterations" is this:

kurupt's solution has a cell that refers to itself. The formula in c1 is set to equal itself under certain circumstances.

If a calculation contains a reference to its own result, it could potentially go round in circles for ever, for example C1=C1+C1/10

In this situation, Excel offers the possibility of repeating the calculation over and over again, until it either hits a maximum number of iterations, or until the value in the cell has stabilised and is no longer changing by more than a fixed small amount.

This allows Excel to handle numerical approximations in which values are calculated as algebraic series, amongst other applications.

In kurupt's case, Excel should realise after a single iteration that the value isn't changing, but if it isn't allowed to carry out even a single iteration, it will throw a wobbly and object to the circular reference.

The difficulty for you is ensuring that any copy of Excel that has to process a cell looking like kurupt's, is set up so that iterative calculations are enabled. If you can do it, Kurupt's method is workable. If not, you'll get errors and the date won't get saved.

 

[ladyck3]

Lionel,

Thanks so much for the explanation. This is being accessed by Excel users who have no clue... so I had given this thought last night... and I'll send out an email with instructions on how to set this.... and request read receipts...

If I do not get a receipt, the end user will not receive the information to access the file.. simple as that.....

I think this will work without having to get into VBA.

Thanks so much for explaining.

LadyCK3
aka: Laurie