radiantjoy
asked on
Inserting Worksheet reference in pasted cells to fix #REF!
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!
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!
StephenJR
Can you give an example of one of them?
ASKER
The workbook is too large to attach, but here's an example:
Worksheet1
Cell F104 =SUM(F9,F13,F14,F15,F17,F2
After copying the cell to Worksheet2, #REF! is displayed:
Cell O62 =SUM(#REF!,#REF!,#REF!,#RE
Worksheet1
Cell F104 =SUM(F9,F13,F14,F15,F17,F2
After copying the cell to Worksheet2, #REF! is displayed:
Cell O62 =SUM(#REF!,#REF!,#REF!,#RE
ASKER
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"=
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.
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
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,WORKSHEE
Thank you both for pondering this!
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,WORKSHEE
Thank you both for pondering this!
Have you got lots of formulae like this?
ASKER
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?
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sorry, crossed post
ASKER
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.
ASKER
The experts all gave me accurate instructions but it is not possible to do what I need to do in Excel.