Is it possible to have a cell calculate itself?

[jambog]

I have the following formula that substitutes a code number for an item name.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1","apple"),"2","banana"),"3","carrot")

This formula works fine if I enter it in any other cell except A1, since it is using the data in A1 to calculate the formula result.

However, I want the user to enter the data in the same cell that calculates the result. For example the formula is in A1, but that is the same cell the user would type "1" in to. Once the user selects the next cell, the "1" in A1 is replaced with "apple"

Currently, I get the circular reference warning when doing this which I disregard, but of course, when I do enter the text in the cell, the formula disappears and is replaced with my entered text.

Are there some advanced settings or syntax I can use to override this behavior in excel and have the cell calculate it's own answer based on data entered into the same cell as the formula?

[kersplash]

You would need VBA to do this I believe.