Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I created a worksheet/spreadsheet with functions and references to cells in other worksheets/spreadsheets within same workbook to create a report of other worksheets within the same workbook.

I have lots of workbooks/spreadsheets on which I have to copy this worksheet lots of workbook, but I when I copy the worksheet to another workbook, excel create references to the workbook where I copied from.

Does anyone know how to avoid excel doing this?

Any help would be very much appreciated.

Regards.

share|improve this question
add comment

2 Answers

Excel is trying to be helpful by keeping the formulas pointing back to the original workbook.

To stop it doing this: 1) "break" the worksheet - do a find/replace across the whole sheet for "=" and replace with something that you know you have not used anywhere ever. "##" might be a good option. 2) Now copy the sheet to where you want it. Excel does not recognise your formulas as formulas since they are now just text entries 3) un-break it using find/replace to restore the = signs. As long as your formulas refer to worksheet names which actually exist in this target workbook, you should be fine.

share|improve this answer
    
Genius! (although I hoped for a solution, not a workaround...) –  ysap Sep 6 '12 at 15:32
add comment

Your question is vague. Relative/absolute cell copying does generally perform as you describe. When you create a formula in excel, by default it is relative.

C1 = A1 + B1 --When copied the referenced cells can change = relative

C1 = $A$1 + $B$1 --When copied the cells do not change = absolute

In this case, the formulas themselves need to be updated to behave accordingly.

share|improve this answer
    
Thanks! do you know how can I use this in the following? =COUNTIF('New Workplan'!J3:J83,C3) as 'New workplan' is the name of the work-sheet.. –  Amra Oct 17 '11 at 15:33
    
Also I am getting errors with =SUM($B$3:$B$7,$E$2,$E$9) I am getting #Value as a result –  Amra Oct 17 '11 at 15:41
    
@Amra - #1 have you tried inserting dollar signs? #2 The Sum works fine in a new worksheet. So there must be something wrong with the specific worksheet. It sounds like you have several more questions. Instead of appending to this question and trying to gather enough detail and then answer them in all comments I recommend you wrap this question up and create new questions. –  P.Brian.Mackey Oct 17 '11 at 15:57
    
My bad, =SUM($B$3:$B$7,$E$2,$E$9) works fine, but the I cant figure out the stylesheet absolute reference. –  Amra Oct 17 '11 at 16:13
    
@Amra - What is the issue? Does 'New Workplan' exist in the new workbook? If not, can you just update the name to whatever it is supposed to be? –  P.Brian.Mackey Oct 17 '11 at 16:30
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.