Pasting formulas into excel

Hi there,

so Excel is like really super pissing me off (sorry for the language). My Problem: I am coding formulas I need automatically in another program which writes them to a .txt file. I want to copy and paste the formula like it is into the cell WITHOUT excel changing a single character. Nothing, not a number not a reference just nothing. Is there any option stopping it from that. This is the most hilarious thing that you cannot write in a cell what you want. I am kinda new to excel and I am sure I miss some things but there needs to be something.

Context: I got folders for every month and it contains an excel file for every day (01.xlsx, 02.xlsx,...). In one file I want to sum over the order of every day. I am at the point where i can write SUM( and click on every cell. But doing this for 1-30 days and 80 products is not possible. So i wrote code that just gives me the formula in the .txt file. Now i need to copy and paste the formula into excel. However, it changes the references of the third and all following paths to the files. I do not know why it does this not for the first and second but all following. Whatever just a super annoying thing.

I would be glad if you can help me with a solution that allows me to copy and paste formulas because I will do this more often. Of course you can also tell me how i may get the sum faster for all the cells instead of clicking. I mean Excel must have an in-built solution?

Thanks in advance,
Tim

Hi,
can you please copy here the formula you are using?

It is:

=SUMME([01.xlsx]Tabelle1!B4;[02.xlsx]Tabelle1!B4;[03.xlsx]Tabelle1!B4;[04.xlsx]Tabelle1!B4;[05.xlsx]Tabelle1!B4;[06.xlsx]Tabelle1!B4;[07.xlsx]Tabelle1!B4;[08.xlsx]Tabelle1!B4;[09.xlsx]Tabelle1!B4;...

I jut it after the 9th day of the month now. Obviously there follow some other days. "SUMME" is just the german word of SUM. If i copy this into the Excel cell it transforms it into:

=SUMME([01.xlsx]Tabelle1!B4;'F:\***\***\01_Jan\[02.xlsx]Tabelle1'!B4;'F:\****\****\01_Jan\[03.xlsx]Tabelle1'!B4;....

This time for whatever reason already the second day.

Just wondering - why do you open qa new file for each day, and not a new tab in same file?
i think it will be easier to sum them up if they are all incorporated in the same file....

Just a personal preference. Don't like Excel files with 30 open tabs and importing an excel file into the existing one seems harder for the other workers.

When you do the copying all files are open? Are they saved in directory 'T' ?

Belinda Will try this tomorrow directly as I often only opened the first and they were the ones where it did not change it.

AliGW
Will also have a look on that but the screen is way to fast gone. Its basically not there

Perhaps when the files are closed the references are changing their address, that's why I also asked you if you have a way of incorporating all your daily sheets to one file,
you will have to import the ones you already created, and going forward - don't open a new file - just add a new tab instead.
Import is not that complicated ...just right click on the tab name --->move or copy--->choose the file you would like to import the sheet to--->check the "create copy" if you don't want to lose the content in the original file, and just create a copy.


Then you can just use this formula to sum same cell across all your sheets:
Say your sheets names are '01','02','03'.......,'12' , and you would like to sum cell B4 - you will use this formula:
=SUM('01:12'!B4)

Sorry for the delay. Yea Excel changes the path if the file is not opened. Pretty interesting... I guess i will go the way with the tabs then.

Thank you for your help.