[Solved] How to batch edit formulas?

[jamster]

Hi,

Firstly I apologize for asking a question that I'm sure could be answered by reading the instruction manual.But I'm a complete newbie to open office and need t a task done A.S.A.P.

I'm using a spread sheet that someone sent me .I'm working on cash 'sheets' that are all entitled consecutive week endings.And the weekly outstanding balance columns are 'linked' from each week .So I want to create a new week ending sheet .At the moment I right click at the bottom of the sheet/tab and select copy/move sheet . A new copy is made.Here is where I get a little stuck .I can see that the 'formula' in the outstanding balance column needs altering so it carries the balance forward from the previous week .In fact I can manual enter the cells formula individual so they show the correct calculated values . I.E - By changing the formula in the cell from " =$'26 Oct 2012'.M34" to "=$'2 Nov 2012'.M34" .Though to do all the rows manually would take far too long.

So my question is how can I alter the values in a column -or rather just alter a particular part of the formula of all the cells in a column (multi-batch editing ?) ?. Or is their a simpler way to copy the whole weeks sheet that will make the newly created sheet calculate correctly - I.E - Bring forward the balance from the previous week .

Thanks for any help with this . I am going to read the beginner guides in more depth as soon as I have time

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).

[keme]

You can change all references to "previous week" from absolute to relative references. The following procedure assumes that weekly sheets are identical, and placed in a row with no sheets in between.

• Start with the original file.
• Save it with a different name, so you have the original as a backup.
• Select the last week sheet. - I assume that is the one named "2 Nov 2012".
• Menu item Edit - Find and replace
• Search for $'26 Oct 2012' - ... if my assumptions above are correct
• Replace with '26 Oct 2012' - just removing the dollar sign
• Click the more options button and ensure that "regular expressions" is not ticked.
• Click Replace all

Now when copying the "fixed-up" sheet for the last week and placing the copy just after the original, you should have a sheet with linking as desired.

If you have already done a lot of work that would be lost if you make a new copy of the sheet, you can also do the replace in the new sheet. Make sure you save your work with a different name to avoid losing your work. The Find/replace operation may make unexpected changes...
Search for $'26 Oct 2012' and replace with '2 Nov 2012'

[jamster]

Thanks for that it worked a treat .

[kingfisher]

If you are using relative references (which I suspect is the case), instead of copying the last sheet, insert a new sheet after it, then copy and paste from the previous last sheet to the new last sheet. All relative references should change automatically.

[keme]

If you are using relative references (which I suspect is the case), instead of copying the last sheet, insert a new sheet after it, then copy and paste from the previous last sheet to the new last sheet. All relative references should change automatically.

They do if you copy a sheet too. Just have to change the absolute sheet references to relative first, like I explained in my first reply. Jamster mentioned a repetitive edit he needed to do, and those sheet references in his explanation are absolute, which is the main reason for his original problem.

With content copy, you lose adjusted column widths unless you make sure to copy the entire sheet, and also outlining (row/column groups) is lost. There may be other things you lose (e.g. pushbuttons, print ranges), I haven't checked.
For jamster's stated requirement, I believe the sheet copy is the safest solution.