Need help with calculation options in excel.

Is there a way that I can paste values into a column and not have my worksheet recalculate?  I need the source values (those that are copied) and the pasted values to remain the same.  The values in the source column are dependent upon cells in another column that are randomly generated.  The act of pasting causes the source values to recalculate; hence the mismatch.  Setting calculation mode to manual creates another set of problems for me.
ronadairAsked:
Who is Participating?
 
AndyAinscowConnect With a Mentor Freelance programmer / ConsultantCommented:
Why copy and paste?
in one cell (where you paste to) have the contents linked to the other, source, cell.
eg. If cell B3 is the value of cell a3 then in b3 just have +a3 as a formula.  (I guess you would have something rather more complex but the principal is the same - no copy/paste is performed)
0
 
Saqib Husain, SyedEngineerCommented:
I don't think you can get over with this. You have to choose between one of the two options: automatic or manual.

What I can suggest is to use VBA to generate the random numbers instead of using excel function to generate the random numbers.
0
 
pcelbaCommented:
You may generate your "random" values outside the Excel and then they'll behave as any other constant values. Use any external data source for it.

Or you may create your "random" values in Excel in some button click code.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
ronadairAuthor Commented:
The simplest solutions are the best!  Thank you.
0
 
pcelbaCommented:
If you are asking how to avoid the sheet recalculation (or random number generation) when you paste something to a cell the correct answer cannot be "Don't paste."

Yes, the simplest solution should be the best but the proposed one cannot work if you still have the automatic recalculation switched on. And you requested it to be switched on.

Simply avoiding the paste operation cannot avoid the sheet recalculation when you write something to a cell. The random function will generate a new value on any sheet change.
0
 
ronadairAuthor Commented:
pcelba -

I see your point, but the answer opened my eyes to the possibility of another type of solution.  And, it worked.

Ron
0
 
pcelbaCommented:
We can just see the incorrect answer selected as the solution which is not good.

You should post your solution and select your post as the answer.

To disable the automatic random values generation in Excel sheet is easy and you don't even need any VBA code to achieve it.
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
>>We can just see the incorrect answer selected as the solution which is not good.


cough cough.  My 'method' is an alternative which removes the source of this problem.  To assume it does not work because the user could do something else later (which there is no indication would actually happen) is rather silly.  Just because something could later be done may invalidate lots of solutions at EE.
0
 
Saqib Husain, SyedEngineerCommented:
I think the accepted solution is both valid and appropriate, and, above all, suits the asker.
0
 
pcelbaCommented:
I would not pay for such solution but that's not my money so do whatever you decide with them... :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.