Copy functions to another Excel file, not working properly?

Camel123

Board Regular
Joined
Jun 5, 2018
Messages
186
Hi,

I created an Excel file with functions/formulas which I keep as a template which is cleared from raw data input. I want to implement the functions in other Excel files and make them work automatically right away. When inserting data input directly in the Excel template, all functions are calculating and updating automatically, this is not the case when I copy the functions and paste it in another Excel file. I get errors such as "#REF" and need to solve it manually. I have tried several paste options but I can still not manage to make it work automatically. At the moment, I need to put the cursor in the function's field and then hit ESC. Then the "#REF" turns to "0". Afterward I need to click "Calculate sheet", in order for the formula to calculate the correct values.

Can anyone please support me in how to solve this problem?
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Now my work around described in my post does not even work, for some reason. When I paste the formula, "Save as" pops up twice. I tried to both save and cancel but the formula just turn to either #value ! or #ref !. This is how the formula looks:

=IF(OR([Costplan]Sheet1!C3="Consumables";C3="Tires";C3="Wheels");SUMIFS([Standard]Sheet1!$N$424:$N$3333;[Standard]Sheet1!$E$424:$E$3333;[Costplan]Sheet1!A3;[Standard]Sheet1!$D$424:$D$3333;[Costplan]Sheet1!B3;[Standard]Sheet1!$B$424:$B$3333;[Costplan]Sheet1!C3);SUMIFS([Standard]Sheet1!$N$424:$N$3333;[Standard]Sheet1!$E$424:$E$3333;[Costplan]Sheet1!A3;[Standard]Sheet1!$D$424:$D$3333;[Costplan]Sheet1!B3;[Standard]Sheet1!$B$424:$B$3333;[Costplan]Sheet1!C3;[Standard]Sheet1!$C$424:$C$3333;[Costplan]Sheet1!D3))
 
Upvote 0
The way the formula looks, sheet1 is the name of the sheet, is it possible you meant that they are the names of workbooks?
 
Last edited:
Upvote 0
The way the formula looks, sheet1 is the name of the sheet, is it possible you meant that they are the names of workbooks?

Sorry, something must have turned wrong when pasting but this is how it should look:

=IF(OR(Costplan!C4="Consumables";C4="Tires";C4="Wheels");SUMIFS(Standard!$N$424:$N$3333;Standard!$E$424:$E$3333;Costplan!A4;Standard!$D$424:$D$3333;Costplan!B4;Standard!$B$424:$B$3333;Costplan!C4);SUMIFS(Standard!$N$424:$N$3333;Standard!$E$424:$E$3333;Costplan!A4;Standard!$D$424:$D$3333;Costplan!B4;Standard!$B$424:$B$3333;Costplan!C4;Standard!$C$424:$C$3333;Costplan!D4))
 
Last edited:
Upvote 0
If I am not mistaken copying the same formulas to a different workbook, the regular copy/paste will create a link to the original workbook.


Select the cells with the formulas and hit Ctrl & H (find/replace)

Find what? =
replace with ? +=
replace all

copy and paste to the destination workbook.

Select those cells and find/replace

find what? +=
replace with? =

replace all.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top