stop updating if condition met

[kangyao]

I am creating a spreadsheet to track stock performance. There's 5 column

• A. StockName
• B. TargetPrice
• C. TodayPrice (update daily)
• D. TargetPriceDate

When TodayPrice hit TargetPrice, I would like TargetPriceDate to display today date and locked itself from further updating even TodayPrice is updated in future dates.

Currently I'm using following formula in column D2
=IF(C2>=B2, TODAY();"")

but it will keep updating on daiy basis so i lost track of what'a the actual date of the conditioned met. is there any solution to this without using VB?

 

[BenMiller]

Not really. If you would set up your data a little differently, it might be possible. Instead of updating your data to replace the old data, let every day's data populate a new column, and then you can easily look up which column has the number that matches your target price.

 

[kangyao]

Can I do it using VBA to fulfil following

There are 5 columns

• A. StockName
• B. TargetPrice
• C. TodayPrice (update daily)
• D. TargetPriceDate
• E. Formula

E2 will evaluate two conditions

1. C2 >= B2
2. ISBLANK(D2)

If condition met, E2 will write the value of Today() into D2 . the date in D2 shouldnt change upon write in.