Saving data

[Formula help]

I have formulas on a worksheet. I also have a Monte Carlo add-in that will
run simulations. When I close the worksheet (workbook) with a given set of
data and the reopen the workbook, a new set of data is shown. This is because
the formulas are initiated when the workbook is open and a new set of data is
generated. I would like to close the workbook and reopen it with the same
data. In other works, I would like to "lock" the date such that it does not
change when I close the workbook and then reopen it.

Any suggestions?

Thank you,

Bill

 

[Shane Devenshire]

Hi,

It sounds like this is the add-in's behaviour, not Excels', in which case
you should look at the add-in.

You might consider copying the results of the simulation to a new range in
the spreadsheet before you save the file.

 

[jamescox]

Elaborating a bit on Shane's suggestion...

Assume that your Monte Carlo (MC) add-in fills a column range with
values that your worksheet formulas process in some way - let's say that
column range is part of the D column. To recap, Col D currently holds
the random values returned by the MC.

Insert two rows to the right of Column D. Excel will adjust your
workbook formulas so that everything still works like it was.

Set up the MC so it places a new set of random values in Col F - the
same number of random values in the same rows in Col F as you have in
Col D.

Replace the MC random values (or MC cell formulas, if that's how your
MC add-in works) in Col D with the following - picking row 3 as an
example

=IF(ISNUMBER(E3),E3,F3)

and copy and paste that into all Col D rows where a MC value was.

What the above formula says, in effect, is "If there is a number in E3,
use that. If E3 is blank or contains something other than a number,
then use what's in F3.

While you are using the workbook normally, you want there to be nothing
in the Col E cells, so your MC addin puts random numbers in Col F and
your existing worksheet formulas - which are still looking at Col D -
use those new MC values.

When you get ready to stop working, select the entire Col F, copy it
and then use Paste Special Values to paste the current MC numbers into
Col E - then save and exit the workbook.

Later, when you re-open the workbook, the same set of values saved in
Col E will now be showing up in Col D - which is what you were asking
for.

Of course, when you want to start using new MC values, you just select
Col E and delete all the values in it.

The process of copying and using Paste Special Values to move a static
copy of the Col F MC values to Col E can be automated to run when the
workbook closes. Use the macro recording feature to get the commands
and then put them in the ThisWorkbook code sheet as part of the
Workbook_BeforeClose event. If you aren't (yet) into VBA code, just
doing it manually isn't all that much trouble.

Hope this helps... :Bgr