Solved

Need help with calculation options in excel.

Posted on 2015-01-09
10
125 Views
Last Modified: 2015-01-11
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.
0
Comment
Question by:ronadair
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40541958
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
 
LVL 44

Accepted Solution

by:
AndyAinscow earned 500 total points
ID: 40541966
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
 
LVL 42

Expert Comment

by:pcelba
ID: 40542028
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:ronadair
ID: 40542365
The simplest solutions are the best!  Thank you.
0
 
LVL 42

Expert Comment

by:pcelba
ID: 40542399
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
 

Author Comment

by:ronadair
ID: 40542460
pcelba -

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

Ron
0
 
LVL 42

Expert Comment

by:pcelba
ID: 40542477
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
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40542721
>>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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40542986
I think the accepted solution is both valid and appropriate, and, above all, suits the asker.
0
 
LVL 42

Expert Comment

by:pcelba
ID: 40543128
I would not pay for such solution but that's not my money so do whatever you decide with them... :-)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Copying directory listing to specific files in another directory 8 30
Need help Creating PowerShell Script 5 64
$_GET call between URL 3 38
using vb script 5 49
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question