0

I'm trying to sum the total number of hours within a set of rows. I've created formulas that calculate the total number of hours based on a weighted probability. At times, totals within cells will need to be moved from month to month. The most efficient way would be to allow a cut and paste function, but when doing this the formulas get messed up. For example, the formulas in all of row 13 below are linked to the probability listed in cell B12. Cell N13 has the the following formula "=$B12N12", cell O13 has "=$B12O12", etc.

IN the below screenshot, I am trying to move the values from N12 through P12 to J12 through L12 without changing formulas in row 13.

enter image description here

When I do I get the #REF! error for cells J13 through L13. How do I prevent the formulas from changing? I have tried locking select cells in the spreadsheet but it doesn't resolve the problem. Please, help.

Improve this question
1
  • 1
    You can try restarting your workbooks (and Excel). A workaround I use from time to time is to copy the formula into Notepad. Then copy from Notepad into the new cell (sheet). It seems to me Excel has extra things in a formula you cannot see.
    – anon
    Commented Dec 9, 2023 at 17:21

2 Answers 2

Reset to default
1
  1. Locking the references (use $ sign) - Since the formulae may contain relative references therefore causing issues when you do copy/paste. You can lock the formulae reference by adding $ signs, not only to the column identifier, but also the row identifier, e.g. =$B$12*N12 in cell N13, and repeat the absolute reference for cells O13 and P13.
  2. Then Paste Special in excel - select the cells containing the values you want to move (N12 through P12), copy them, select the target cells (J12 through L12), right-click, choose "Paste Special," select "Values," and opt for "Paste Link." This process pastes the values while creating linked references.

Hope that's what you wanted.

Improve this answer
0

When you move formulas from one place to another they are not changed. They may be changed if you copy/paste them and formulas contain relative references.
To prevent changing them in this case you can convert them into text, copy text and then convert again into formulas.
Another way is to use Ctrl+` to see formulas instead of results, then copy/paste cells using Office clipboard (not standard Windows clipboard), and then use the same combination to return to the standard view.

If you were going to multiply these cells and you wanted to cut and paste the cells with the data used by the formulas, try this formula:

=$B12*INDIRECT("R[-1]C",)

Cut and Paste Data

Improve this answer
2
  • Thanks for the ideas, but we are going to be moving data constantly in this document and cut and paste would save all users a significant amount of time. I've tried a few of these suggestions and they do work when I copy and paste but do not work when I cut and paste.
    – hilbig09
    Commented Dec 19, 2023 at 16:13
  • The answer was general, because it is difficult to understand what exactly is going on. The image of the screen is illegible (it's better to show a fragment of it, but clearly), I cannot understand the meaning of: Cell N13 has the the following formula "=$B12N12", cell O13 has "=$B12O12", etc.
    – MGonet
    Commented Dec 19, 2023 at 17:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .