We help IT Professionals succeed at work.

Inserting Worksheet reference in pasted cells to fix #REF!

radiantjoy
radiantjoy asked
on
I would like to copy and paste some cells from Worksheet1 to Worksheet2. These cells contain SUM formulas that references cells in Worksheet1.

When pasting these cells (I've tried every option in Paste Special), I get a #REF! value. I do not wish to paste as link as I will be deleting the copied cells from Worksheet1.

Is there an easy to way, without VBA, to copy and paste these cells to reference Worksheet1?

I must apologize - it seems to be simple but I confess that I've not been able to find the answer in my internet searches this morning.

Thank you in advance for your patience and help!

Comment
Watch Question

Can you give an example of one of them?

Author

Commented:
The workbook is too large to attach, but here's an example:

Worksheet1
Cell F104  =SUM(F9,F13,F14,F15,F17,F25,F27,F30)

After copying the cell to Worksheet2, #REF! is displayed:
Cell O62  =SUM(#REF!,#REF!,#REF!,#REF!,#REF!,#REF!,#REF1,#REF!)


Author

Commented:
Also, I am not certain this makes a difference but each cell F9,F13,F14, etc. value displays a value from another worksheet, such as =SUMPRODUCT((NAMEDRANGE1"="VALUEA")*("NAMEDRANGE2"="VALUEB"))

I think the problem is that they are relative references, and when you move them from row 104 to 62 some (all?) of those references would be negative rows.
CERTIFIED EXPERT

Commented:
The reason they are being treated as an error is because excel can no longer identify what cells they ought to refer to - you arent pasting a link and having them point to (for example) F9, f13 on sheet2 when they used to refer to f9, f13 on sheet1 would make the sum meaningless.

If you only want the values - use past value - if you want to preserve a copy of the formulas why not copy the entire tab (right click tab name and move/copy) and delete the bits you dont need?

reg

So what are you trying to do, and why can't you just paste the values?
Excel VBA Developer
CERTIFIED EXPERT
Top Expert 2014
Commented:
This is happening because the relative differences in the rows between your original formula's location and the values it is referencing will cause a negative relative value when copied to a row that is less than row 95 - which is happening in your new location (row 62).

If you want to copy the formula exactly, instead of copying the cell and pasting in a new location, do one of the following:
1) Highlight the formula in the formula bar on top, and copy it ([Ctrl]+[C]),
press [Esc] to exit the formula bar,
move to cell O62
paste the original formula back in the formula bar and press [Enter]

2) Change the relative references in the original formula to absolute references (use the F4 key as a shortcut for this) so that the formula looks like this:
=SUM($F$9,$F$13,$F$14,$F$15,$F$17,$F$25,$F$27,$F$30)
NOTE:  This is only preferable if these cells are treated like constants and are not part of a larger set of data to be calculated.
If you just want the formula in sheet 2 to refer to the same cells as in sheet1 you'd either have to make the references absolute, or use VBA.

Author

Commented:
StephenJR- Thank you for your answer - Rather than pasting the values, my desire is that the cells be updated in case F9, F13, etc. are updated.

regmigrant - Thank you for your guidance, I had thought of that but was hoping that there would be another way to move these cells without copying the page and deleting the cells not needed for the calculation.

So other than redoing the caluclations in WorKsheet2 (e.g., Sum), then jumping over to Worksheet 1 and selecting the cells to populate Worksheet1 062, there is no easy way to populate the Worksheet1 reference into each cell (e.g., without vba) to read: SUM(WORKSHEET2!F9,WORKSHEET2!WORKSHEET2!F13,WORKSHEET2!F14,WORKSHEET2!F15,WORKSHEET2!F17,WORKSHEET2!F25,WORKSHEET2!F27,WORKSHEET2!F30)?

Thank you both for pondering this!




Have you got lots of formulae like this?

Author

Commented:
yea, unfortunately, however, if there is no other way to make the references absolute, I'll suck it up and get started on it.
Why not use VBA?

Author

Commented:
i'm not terribly skilled at VBA, I'm ashamed to admit, and since I'm not the owner (only a contributor) to this massive workbook, I didn't want to add anything to the back-end.
CERTIFIED EXPERT
Commented:
I think stephenJR answered this - you could make the references Absolute by selecting each one and pressing f4, then when you copy they will still refer to the same location $F$9 etc in the new sheet.

But your example suggests tou are looking for a link from worksheet 1 to worksheet 2? - "cells be updated in case F9.. are updated."


Reg
CERTIFIED EXPERT

Commented:
sorry, crossed post

Author

Commented:
regmigrant and StephenJR: Thank you both for your time - it sounds like there is no easy way to do this. Thank you for your time.

Author

Commented:
The experts all gave me accurate instructions but it is not possible to do what I need to do in Excel.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.