A Value and a Formula result in the same cell

[bye013]

Dear experts,
I want to write a value in cell A1 and immediately when I will press Enter (or click outside the cell) I will receive a result of a formula that uses this value.
This is trivial to do when you want the result of the formula in A2 or any other cell besides A1. The challenge is that I want the result of the formula in the same cell that I’ve placed the value (i.e. in A1).
The formula itself doesn’t have to be written in the same cell, but I thought that the solution will be by using some kind of a cell format (that includes the formula). Anyway I couldn’t find a way how to do it.
Appreciate very much if anyone can help me by instructing how to do it or by providing a VBA.
Thanks very much,
Yaron.

[Marcol]

You can either have a formula or a value in a cell, it isn't possible to have both.

You can however simulate your conditions by using VBa and the Worksheet Change Event, where a formula could change the entered value to the desired value.
This very simple code will multiply the value you enter in A1 by 5

Please Login or Register  to view this content.

Remember to use Application.EnableEvents = False at the beginning of the code and Application.EnableEvents = True at the end.

[bye013]

Thank you very much Marcol. It seems to be the code that I was looking for.
Regards,
Yaron.

[JosephP]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save