Excel Question

Thank you I will try that.Will that put in a new date thou everytime you open excel?

I think you've correctly identified a problem with MPM's answer.

If you use FrankGon4s "NOW" and format the cell as time (just to keep it simple), you can quickly experiment with different self referential (or cyclic reference) conditionals, but, I still can't see any way out of this.

I think Randall hit the nail on the head. Every time Excel 'recalculates', it would update any formula to the current date/time. If I understand correctly, once the date is posted, it should not altered further.

Escalus came up with a programmatic approach that looks like it will work well as long as the word "complete" is only used in the specified column, otherwise it would put the date next to where ever it encountered the word "complete". Another possible side effect may be if a cell already has the word "complete", and a formula (or anything else) changed the cell to "complete", e.i. no change at all, if the event for the worksheet would still execute and update an existing date. Perhaps Escalus can comment on this.

Using a combination of formula and VBA code, an 'if' statement could execute code that pastes the current date value to the cell, replacing the 'if' statement in the cell and preventing any further alteration.

Hi Maths Physics Maniac,

Good answer.

Any ideas of how you can stop the cell formulae result from changing to the current date after being completed some time before?

Its got me stumped, and I don't even play cricket!

Paulus

Hi DCaD,

Thanks for the ideas, I will have a play and see how I get on, it looks promising.

Paulus

Hi Frankgon4,

I try your indication with NOW, but can´t set the format cell to date and time at once, it is posible? Could you explain?

Regards

Ernesto1962

Frankgon4,

Thanks by your explanation.

My Excel version at office don´t have this format for date.

I will try later at home, I have a laptop with Vista (as look your example).

Regards and thanks again

Ernesto1962

Hi FITZ,

In addition to the formula shown by MPM, you might also check out the help topics under "Conditional Formatting". This offers some excellent tools as well to visually show states and conditions. In my work, we also track various issues by status, and in addition to something being "complete" or not, it's good to look at "pending", "critical", etc. (for instance), and with conditional formatting you can show everything from "RED/YELLOW/GREEN" (such as a traffic light), or gradients of "Blue to Red" (hot or cold), and so forth.

Excel offers some great visual tools for allowing one to see at a glance if or if not something needs attention.

Good question, and Good Answer by MPM.

Kind regards .......

I will look at those as well and thanks for the input about the colours

=If (A1 = "Complete",NOW(),"")

works.

The problem is that now means just that and the date you get will update to the current one whenever the sheet is recalculated. I'm guessing this isn't what you want.

If you want to change the value of a cell to the time at which some condition was first met, you will need to resort to a macro program.

Take a deep breath...

For Excel 2003: Paste the code (everything below the stars) underneath into the vbaProject (do tools>customize>tool bars and check the Control Toolbox box. This toolbar has a "View Code" button on it that looks like a magnifying glass and a piece of paper. Press the button and double click the worksheet icon for the sheet you are working in. This opens its VBA code page)

The effect of this code will be to check the sheet whenever it changes and if the value "Complete" appears, it will enter the current date and time into the cell to the right of it as a fixed value that doesn't change. It fails if you change multiple cells at once and doesn't clear dates if the the "Complete" entry is removed

If you are using Excel 2007, the best advice is to revert to 2003 for this kind of thing. Good luck.

*****

Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Count > 1 Then

MsgBox "Automatic date entry on 'Complete' fails when multiple cells are changed. Sorry."

Else

If Target.Value = "Complete" Then Cells(Target.Row, Target.Column + 1) = Now()

End If

End Sub

For Excel 2003, if the cell already has the word "complete", and a formula changes the cell to "complete" (just re-enters what was already there), would the worksheet change event execute altering the already posted date for that cell?

I tried your code using Excel 2007, and the date did update if you retype "Complete" over a cell which already has the word "Complete"

This is simpler. Counting the number of entries in the column first would let it grow too. Does application.count(range) work in 2007..?

On the downside, simply entering or pasting "Complete" into the A cells doesn't necessarily force recalculation and therefore, in that situation, nothing will happen.

If you're asking about a Range, then

Range.Count returns number of Cells in Range

Range.Columns.Count returns number of Columns

Range.Rows.Count returns number of Rows

This works for a Range that has a single area, otherwise these return the counts from the first area.

A reply to post #14 said it did calculate. I don't have Excel 2007, so I could not properly test the code.

Lendog,

Thanks for testing the code in 2007. You are correct and it's not the only weakness either. If the target cell is a formula that produces the word "Complete" it will probably fall over there too. Watching for sheet changes isn't completely fail-safe (the quirks in 2007 may well be different than those in 2003) but it's likelier to trap things than watching for "on calculate".

The code below is better provided that it is some cell in the SAME row as the one you want updated that causes the changes in the FIXED columns used for the word "Complete" and the date entries (assumed to be columns 1 and 2 in the example). It also clears dates if the change made to the row results in the word "Complete" disappearing. This would be un-undoable.

To prevent direct changes to formulas that generate "Complete" and to the date entries themselves, I would normally use ActiveSheet.Unprotect at the begining and and something like ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True at the end. The specific cell protection formats in the sheet need to be set correctly (by hand) and I have no idea what the application looks like.

In general the original point stands; there is no good way to mix dynamic and static data updates in Excel without resorting to code. It is also true that when resorting to code there is no "right" way to do it - just good and bad ways depending on the context and the user. It sounds to me as though if you worked this one through you'd end up with forms and all sorts to stop everything that could go wrong from going wrong. I think the original poster is baffled enough by now by the numerous workarounds on offer...but it was fun anyhow.

Let me know if this is 2007 proof...

;-)

*****

Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim Row As Integer, Col As Integer

If Target.Count > 1 Then MsgBox "Automatic date entry on 'Complete' only changes the top row when multiple cells are changed."

Row = Target.Row

Col = 1

'Change Col to a different value to move the "Complete" column. The offset in the formulas below is +1 - i.e. the next column.

If Cells(Row, Col) = "Complete" And Cells(Row, Col + 1) = "" Then

Cells(Row, Col + 1) = Now()

Else

If Cells(Row, Col) <> "Complete" Then Cells(Row, Col + 1) = ""

End If

End Sub

Escalus,

When I was playing with the code, I ran across the formula bust, too. It give a type mismatch error when attempting to compare a non-text value to a text value. Sometimes I learn more from bug hunts than anything else...

I ran the code in 2007, and despite everything looking just fine, I ran into the strangest thing. When the event is called, the code executes until it gets to a line that changes the contents of a cell, and the act of changing the cell re-calls the change event. The execution runs to this line:

If Cells(Row, Col) <> "Complete" Then Cells(Row, Col + 1) = ""

If the cell is not "Complete", then blank the next cell. This calls the change event again, which runs this line, makes a change, calls the event, and loops until it errors out with a script out of range. The event calls itself so many times it crashes, and I have to restart 2007 to get the event to call again. I altered the line to

If Cells(Row, Col) <> "Complete" And Cells(Row, Col + 1) <> "" Then Cells(Row, Col + 1) = ""

The event calls itself until no more changes are necessary (twice from what I could see) and exits normally. I did not know (until now) that an event could trigger itself.

I have to agree that there is no right way to do this. I did have fun with it, and enjoyed the challenge. You made me learn something...

Thanks for the info.I have not used code or macros much but I tried it and pasted in the code you provided into the worksheet,as per instructions,but I did not get a date to appear in the cell next to the complete.I am using excel 2000 would this make a difference or maby I am doing some thing else wrong?

Thanks

FITZ,

I don't know how adventurous you feel, but I have a approach that will give you a hard date once the target cell is changed to "Complete", and will not update further after that point. First, a piece of VBA code. Hitting Alt F11 will bring up the VBA code window, and double clicking on the sheet you wish to use will open a code window. In the code window, paste the following:

Public Sub PostDate()

With ActiveCell
.Value = Today()
.Copy
.PasteSpecial (xlPasteValues)
End With

End Sub

This code places a formula which gives the current date, and then copies and pastes the date value to the cell. To use this code, use the following statement where needed;

=IF(**address of target cell** = "Complete", PostDate(),"")

Executing "PostDate" from the 'If' Statement replaces the statement with a value representing the current date. If you wish to change the date after this, it must be done manually, or by re-entering the if statement.

Until something more simple is found, why not just enter the date-of-completion, in the appropriate cell, such that it has an apostophe at said date's beginning?...

(e.g.: '12/02/2008 or 'Dec. 12,2008)

This guy is worth listening to. Poor automation results in bigger headaches and failures than just living with (and checking for) the human errors you were trying to avoid in the first place.

It all depends on how many cells we're talking about. Just a few, then entering a date would save the headache. Just a few thousand, maybe the investment in time is justified in the long run. I don't know the full scope of Fitz's problem....

One other thing. My code above should have Now() instead of Today(). My original code was based on Now(), and my last minute substitution for the sake of the post proved to be a fatal error when I went back to test it.

When I went back to the same code, which ran perfect earlier, now has some kind of bug which prevents it from successfully running at all - I have yet to figure it out.