Freezing a value in a cell under certain condtion

[HELLO2468]

Hi there.

I want a value in a cell to freeze under a certain condition.

E.G

In cell 'A1' I have the item name, when that item sells I simply add a slash to the item name so it becomes 'Item/' that indicates a sold item to the rest of my excel calculations, in A2 is the price which it gets from my stock sheet, this price fluctuates often so one week it could £10 then next £11 etc.

what i need simply is this: When A1 has 'Item/' (That is item sold) i want A2 not to updated anymore and the price to be frozen, otherwise the price i sold the item for continues to change well after i have sold it!

At the moment I have to manually do it by just typing in the price of the item in A2 overwriting the formula that it uses.

Any suggestions no matter how complicated are welcome.

Thx in advance.

[rcm]

The formula should test for the

=right(a1,1)<>"/"

to act otherwise copy and paste as text the value at a2

[TMS]

I suspect the only way you can do that is with a Worksheet Change Event. When the cell being monitored has a "/" on the end, it would convert the formula to a value ... freezing it.

Regards, TMS

[LJMetzger]

Hi HELLO2468,

I agree with rcm and Trevor. See the attached file that implements what you requested. There are two sheets:
a. Sheet 'Sheet1' which contains item name in Column 'A' and price in 'Column B'.
b. Sheet 'StockSheet' which contains the Master Item List in 'Column A', and the current (volatile) price in Column 'B'.

Instead of using a regular range for the Vlookup on the data in the 'StockSheet', I defined a 'Named Range 'myVookupRangeOnStockSheet', which makes using formulas and coding a little easier. To create a Named Range: Insert > Name > Define > xyz (then select range, then add)

Lewis