Copy functions to another Excel file, not working properly?

[Camel123]

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?

 

[davesexcel]

What function are you working with?

 

[Camel123]

what function are you working with?

if/ifs/sumifs.

 

[Camel123]

Anyone? Need a solution urgently :/

 

[Camel123]

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))

 

[davesexcel]

What is "Costplan" and "Standard"?

 

[Camel123]

What is "Costplan" and "Standard"?

The name of the sheets. Sorry I did not mention.

 

[davesexcel]

The way the formula looks, sheet1 is the name of the sheet, is it possible you meant that they are the names of workbooks?

 

[Camel123]

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))

 

[davesexcel]

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.