Formula help

[JAMES.GOFF25]

Hi

I want to use a formula to trace the date when a cell is populated

I have come up with a formula but I want to make sure that the date will stay static.

For example: This formula =IF(A2="","",TEXT(TODAY(),"dd/mm/yyyy"))

will populate todays date when I enter text into column A. I won't to make sure that when I open my spreadsheet on a later date the date will remain the same as the date I enterd

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=64>A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64 x:fmla='=IF(A1="","",TEXT(TODAY(),"dd/mm/yyyy"))'>17/08/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>B</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:fmla='=IF(A2="","",TEXT(TODAY(),"dd/mm/yyyy"))'>17/08/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>C</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:fmla='=IF(A3="","",TEXT(TODAY(),"dd/mm/yyyy"))'>17/08/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>D</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:fmla='=IF(A4="","",TEXT(TODAY(),"dd/mm/yyyy"))'>17/08/2011</TD></TR></TBODY></TABLE>

Do I have the correct formula for the date to remain static ?

Thanks
James

 

[kpark91]

I do believe it will remain static as long as you don't press F9 (recalculate workbook).

However, usually if it's a complicated sheet, you should copy + paste them as values as you don't want to take any chances .

 

[JAMES.GOFF25]

Do you think there is a better formula than this to perform the task I require ?

 

[Sparkle99]

My 5-cents...

As you are aware, TODAY() will always be re-calculated.

A suggestion is to have some code that runs on File-Save. This code could copy/paste-values of the column with the dates.

 

[JAMES.GOFF25]

Sparkle99:

How do I perform your suggestion ?

James

 

[sonumm]

plz another question

sir can you help me for generates samples sets in excel plz plz help me my set is consists six integers and it has 720 combination to write sample set i cant understand how can i generate sample like my set={1,2,3,4,5,6} and i have to make 720 combination like {6,5,4,3,2,1} plz help me to genrates these sample set on excel 2007 i will be very thankful to you.i m waiting for your reply........as sooooooon as possible plz i have no enough time for my assignment

take care

 

[Sparkle99]

Is what you're trying to achieve a sort of an audit as to when the row changed
i.e. if cell A5 changes, then store today's date in B5 - and so on down the rows.

Try this code...

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A2:A999")) Is Nothing Then
'Target is the value in column A that has caused the change
changedrow = Columns("A").Find(What:=Target, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
'Put today's date into column B for the changed row
Range("B" & CStr(changedrow)).Value = Date
End If

End Sub

-----------------------------------------------

There must be a more elegant way of doing this - I just knocked this up quickly.
The logic says that on a change, the code will search Column A (bottom to top) looking for the value just entered.
So, if the sheet has 20 popultaed rows, and then you change row 5 - there's a chance the value you entered might already exist in a lower row.