Is there anything as Static Data

all4excel · 04-22-2007, 06:21 AM

In excel whatever stored in the cell is stored at that instant and we can work only on that data,what if we want something like a date or any data using the functions --now() or today() but to have the date of the first time and not change everyday...

i really dny have an example rite now but there r so many progrmng scenarios when we come across this situation...

**mudraker** · 04-22-2007, 08:03 AM

There is no such thing as a static answer for a formula.

If a cell has a formula it is subject to change evertime Excel recalulates.

You can use macros to input a date in a specific cell when another cell or range of cells is changed or when a sheet is activated.

all4excel · 04-22-2007, 08:33 AM

I am vry good in excel programming and have not used MAcros,Pivot Tables i want to start using them but dont know how to start...do u have any small examples to help me...

Hope not asking too much..
Thanx

**mudraker** · 04-22-2007, 08:51 AM

This macro - when installed correctly will run everytime a change is made to a cell within the sheet -suggest you test in a new workbook

If the change is made to cells a1, a2 or e3 it will insert the now() value into the next cell to the right (b1, b2, b3)

'These instructions pre typed & are worded to cater for the novice programmer
'To install macro to correct location

'Copy this macro
'GoTo Excel
'Select sheet this is to appy to
'Right Click on Sheet Name Tab > select View Code
'Past macro into the Worksheet Module displayed

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Application.Intersect(Target, Range("a1:a3")) Is Nothing Then
      Application.EnableEvents = False
      Target.Offset(0, 1).Value = Now()
   End If
   Application.EnableEvents = True
End Sub

Now make changes to cells on the sheet