+ Reply to Thread
Results 1 to 6 of 6

Autoamtically replacing parts of the formula while leaving other parts intact

  1. #1
    Registered User
    Join Date
    01-18-2013
    Location
    Lithuania
    MS-Off Ver
    MS Office Professional Plus 2010
    Posts
    13

    Autoamtically replacing parts of the formula while leaving other parts intact

    Hello.
    I have calculated myself into a corner.
    I have been calculating different things in in 3 tables, and there is one that sums it all up. however the one that sums it up is more abstract, therefore some formulas have to sum multiple numbers. So i have used a template and add-pasted them. It works, however the way excel does it is very messy.
    Example:
    =((SUM('use (Trans M)'!C158:D158))+(SUM('use (trade Wh)'!C158:D158)))+((SUM('use (trade R)'!C158:D158)))
    As you can see, there are a lot of unnecessary () marks that is there because excel seems to () every time one adds stuff.

    So what i want, is to clean up the formula, because when you make files with 30 sheets it gets messy real soon. simply removing the () would be fine, if not for the fact that the table has over 41000 cells in it with that, which is a HELL of a work.
    Simply replacing it also does not work because of the nature of the formulas.
    One may be one, another may use different numbers (examples):
    =((SUM('use (Trans M)'!C158:D158))+(SUM('use (trade Wh)'!C158:D158)))+((SUM('use (trade R)'!C158:D158)))
    =((SUM('use (Trans M)'!E158))+(SUM('use (trade Wh)'!E158)))+((SUM('use (trade R)'!E158)))
    =((SUM('use (Trans M)'!F113:F113;'use (Trans M)'!F115:F115))+(SUM('use (trade Wh)'!F113:F113;'use (trade Wh)'!F115:F115)))+(SUM('use (trade R)'!F113:F113;'use (trade R)'!F115:F115))

    When you look at the bolded parts you will see that the nature of formulas do differ a lot and this cannot be solved with a find and replace function. or at least i cant think of a way.
    I have tried using the * mark, and while it finds things correctly, it tries to put * instead of, say, C158:D158 when replacing, so that leads to bad formulas. I am out of ideas, so please help me.

  2. #2
    Registered User
    Join Date
    11-16-2012
    Location
    adelaide
    MS-Off Ver
    Excel 07/10
    Posts
    56

    Re: Autoamtically replacing parts of the formula while leaving other parts intact

    umm...I dont know if this is helpful but are you trying to add Column C through to E from differently names sheets into a Master Sheet?

    If yes, would this work: "=sum('sheet1name:lastsheetname',C158:E158)

  3. #3
    Registered User
    Join Date
    01-18-2013
    Location
    Lithuania
    MS-Off Ver
    MS Office Professional Plus 2010
    Posts
    13

    Re: Autoamtically replacing parts of the formula while leaving other parts intact

    That does not seem to work, it tells me there is a reference error and does not calculate. However that would not help much, as i would still have to replace every cell manually. unless.... automatically replace one sheet name with this, then atomatically remove all added sheets. yeah if only it would go around the reference error.
    ALso, i dont know if this is important, the sheets does not go in a row, there is like
    sheet1 sheet2 sheet3 sheet4 sheet5 sheet6 sheet7 and i need to have sheet2+sheet4+sheet6 in sheet8

  4. #4
    Registered User
    Join Date
    11-16-2012
    Location
    adelaide
    MS-Off Ver
    Excel 07/10
    Posts
    56

    Re: Autoamtically replacing parts of the formula while leaving other parts intact

    Can u attach a sample file.
    Would be helpful to others reading the post even if I cant completely resolve it?

    Thanks.

  5. #5
    Registered User
    Join Date
    01-18-2013
    Location
    Lithuania
    MS-Off Ver
    MS Office Professional Plus 2010
    Posts
    13

    Re: Autoamtically replacing parts of the formula while leaving other parts intact

    Due to confidential nature of it (its for work) i cannot do that. Canibalizing file enough to hide the information would be huge work as the formulas are intertwined within sheets and even files outside it with links, its an excel file that you work whole year on (theoretically anyway) so i cant do that, much anyway. once i get home i may try making a simple sample, but only excel 2003 at home, so it may not have all features.

  6. #6
    Registered User
    Join Date
    01-18-2013
    Location
    Lithuania
    MS-Off Ver
    MS Office Professional Plus 2010
    Posts
    13

    Re: Autoamtically replacing parts of the formula while leaving other parts intact

    So i made a very simplistic example. attacking it.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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