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