Keeping formula results even after refreshing Excel?

[bisctboy]

Hi,

First post on here...wish me luck, LOL.

I want to save the results of a formula even after I hit the F9 key to refresh (recalculate) my formula. Is there a way to do this?

For example, cell A1 has a formula of "=rand()*rand()". I would like to have the results of this go to cell B1. Easy enough so far! However, I want to be able to hit F9 to recalculate the formula in cell A1 but not change the previous result put in B1. To take it a step further, I would like to hit F9 5 total times and put each subsequent result in 5 different cells (B1:B5) without losing the previous results every time I refresh the worksheet...is there a way to do this?

Thanks!

[modytrane]

you need VBA to do this.

Post a sample file with data and explain what you need. Enter results manually to show where and how results should be stored.

[bisctboy]

I have attached an Excel file with the formula in A1. I have commented B1:B5 "Result 1, Result 2,..."

Thanks for your help!

[modytrane]

see attached file.
Click Button instead of F9.

A1 will be calculated [same as F9] and results will be stored in B1:B5.

Here's the macro used:

[bisctboy]

That works and thanks for your help!

[modytrane]

1.Mark your thread as SOLVED when question is resolved
2.Click on the * Add Reputation if you think this helped you