Stopping a formula once a condition has been met.

[ordnance1]

Cell A1 of my worksheet contains the following formula:

='[WTA Work History Overall.xlsx]Retirement Summary'!Retirement_Payment

Cell A2 contains a date. Is there any way that the formula in cell A1 can be
constructed so that once the date exceeds the date in cell A2 that the
formula stops updating the value in cell A1 and just shows the value that
was in place prior to exceeding the date in A2?

 

[Simon Lloyd]

A formula can only return a value so with something like the following
=IF(DATEVALUE("29/11/2009")>A1,"A1 expired",A
If the date in A1 was before todays date then A1 expired will displa
otherwise the contents of cell A1 is displayed, if you want it trul
static you will need to used VBA to make it static like this

Code
-------------------
Private Sub Worksheet_Activate(
If Me.Range("A1").Value > Me.Range("A2").Value The
Me.Range("A2") = Me.Range("A2").Valu
End I
End Su
-------------------
The above code goes in the worksheet code modul

*How to Save a Worksheet Event Macro*
1. *Copy* the macro above placing the cursor to the left of the cod
box hold the *CTRL & Left Click,* then *Right Click* selected code an
*Copy.*
2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab
for the Worksheet the macro will run on.
3. *Left Click* on *View Code* in the pop up menu.
4. *Paste* the macro code using *CTRL+V*
5. Make any custom changes to the macro if needed at this time.
6. *Save* the macro in your Workbook using *CTRL+S*

Cell A1 of my worksheet contains the following formula

='[WTA Work History Overall.xlsx]Retirement Summary'!Retirement_Paymen

Cell A2 contains a date. Is there any way that the formula in cell A
can b
constructed so that once the date exceeds the date in cell A2 that th
formula stops updating the value in cell A1 and just shows the valu
tha
was in place prior to exceeding the date in A2

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com