+ Reply to Thread
Results 1 to 11 of 11

Pasting formulas into excel

  1. #1
    Registered User
    Join Date
    01-22-2021
    Location
    Germany
    MS-Off Ver
    pretty new one
    Posts
    5

    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

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,587

    Re: Pasting formulas into excel

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

  3. #3
    Registered User
    Join Date
    01-22-2021
    Location
    Germany
    MS-Off Ver
    pretty new one
    Posts
    5

    Re: Pasting formulas into excel

    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.

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,587

    Re: Pasting formulas into excel

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

  5. #5
    Registered User
    Join Date
    01-22-2021
    Location
    Germany
    MS-Off Ver
    pretty new one
    Posts
    5

    Re: Pasting formulas into excel

    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.

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,587

    Re: Pasting formulas into excel

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

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,497

    Re: Pasting formulas into excel

    Please update your forum profile from 'pretty new one' to the version you have (it tells you on the splash screen when you launch Excel). Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  8. #8
    Registered User
    Join Date
    01-22-2021
    Location
    Germany
    MS-Off Ver
    pretty new one
    Posts
    5

    Re: Pasting formulas into excel

    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

  9. #9
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,587

    Re: Pasting formulas into excel

    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)

  10. #10
    Registered User
    Join Date
    01-22-2021
    Location
    Germany
    MS-Off Ver
    pretty new one
    Posts
    5

    Re: Pasting formulas into excel

    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.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,497

    Re: Pasting formulas into excel

    OK - so once Excel is open, go to File | Account ... and see what it says under product information. Then you can update your profile as requested.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel VBA is pasting formulas and not values
    By YukonSoDank in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-09-2019, 09:26 AM
  2. [SOLVED] Copy/Pasting an excel sheet with the same formatting/formulas in the same work book to x
    By Morallach in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-20-2014, 02:57 AM
  3. Excel crashing when Pasting Formulas as Values
    By XOR LX in forum Excel General
    Replies: 1
    Last Post: 11-08-2013, 11:48 AM
  4. Formulas Not Pasting
    By AmberM in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-20-2013, 05:38 PM
  5. Excel not pasting formulas properly
    By DebSki in forum Excel General
    Replies: 8
    Last Post: 02-16-2012, 01:37 PM
  6. Replies: 0
    Last Post: 11-15-2007, 02:41 AM
  7. Replies: 3
    Last Post: 03-26-2005, 11:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1