Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving data
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving data
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Formula help" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving data
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 -- jamescox ------------------------------------------------------------------------ jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119546 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
saving data? | Excel Discussion (Misc queries) | |||
saving a data item | New Users to Excel | |||
Saving data | New Users to Excel | |||
Saving Data | Excel Discussion (Misc queries) | |||
Saving transfered data | Excel Worksheet Functions |