Copying formulas from one xlsx to another

[sigfried]

Dear community,

I would be very happy if you could help me with the following problem.

Introduction: I have 50 xls(x) files, which have all the exactly same amount of tabs, which have all the same denomination. This file has been used as a questionnaire and, in the last tab, there is a summary of all answers given in the other tabs. As there was an error in this tab, I need to replace all formulas (approximately 200 cells). In order to do this, I have updated the formulas in one of the documents (which is now working correctly) and want to copy this to the other files.

However, if I copy all cells from the tab of file (1) to file (2), the cell/formula is reffering/linking to file (1). The formulas change e.g. from (1) ='info stud.'!M5 to (2) ='[filename1.xlsx]info stud.'!M5 after I copied it.
1) it does NOT work if I copy and click on [Start] [Insert] [Formula]. The result is the same
2) it does NOT work if I change the properties of the cell from Standard to Text before I copy the formulas. The result is the same
3) it does NOT work if I search for and try to replace e.g. [filename1.xlsx] in the document - an error message appears (it askes me to "update values" and I have to click on Cancel 200 times = number of cells)
4) it does NOT work if I click on [Data] [Edit hyperlinks] (not sure if this is the correct translation. German: [Verknüpfungen bearbeiten])

What should I do in order to have a working summary tab in every one of the 50 xls(x) files?

Thank you.

[FDibbins]

Hi and welcome to the forum

This is 1 of those irritating quirks of excel

Highlight the formula in the formula bar and copy (CTRL C). escape from that, go to the nexy file, click teh formula bar there and paste (CTRL P). Once you have it in 1 cell, you can copy as usual

[sigfried]

Thank you, Ford.
Your solution worked well. Now I just need to do this 50 times. :-)

Cheers.