At 19:21 01/07/2018 -0400, Michel Donais wrote:
I have a huge spreadsheet and have to change a value in all formulas. formula are like this: each columns are this style 'a' identify a column '2-4-5-6' etc are the lines
Er, that's how spreadsheets are!
=sum(a3*e3)+(b4*e4)+(a5*e5)+(a6*e6)+(a7*e7)+................. and so on until (zza256)
Let's start there: that's not a sensible formula. I'm guessing that you think the SUM() function is necessary because of all those additions you need. But no: you can *either* use plus signs *or* else use the SUM() function: it makes no sense to try to use both. In any case, as it is, your SUM() function encloses only the first parenthesised expression, so you are asking SUM() to add just A3*E3. But you cannot add a single item: you need at least two for that! In addition, all those parentheses are unnecessary (and therefore confusing), since multiplication precedes addition in mathematical priority. You can sensibly express this formula in one of two ways, either:
=A3*E3+B4*E4+A5*E5+A6*E6+A7*E7+... or: =SUM(A3*E3;B4*E4;A5*E5;A6*E6;A7*E7;...)
I have to change 'e' for 'b' in about a hundred columns and 600 hundred lines.
Er, sixty thousand rows?
... next row is to change 'f' for 'b' and so on. Is there an automated edit solution that can avoid me to do it by hand?
You can use Edit | Find & Replace... (or Ctrl+F) to edit formulae. Click More Options and ensure Formulae is selected for "Search in". It may help to tick Tools | Options... | OpenOffice Calc | View | Display | Formulae - very probably temporarily - so that you can see the formulae that you are editing.
But it is a Very Bad Idea to edit large numbers of presumably related formulae manually and individually in this way. It is impossible to enter large numbers of formulae without introducing errors - and it is generally not possible to spot such errors. Even if you made no mistakes, you could not be sure that this was so. Instead, you should create an appropriate formula once and use it to fill cell ranges, either by pasting or by dragging the fill handle or by using Edit | Fill. You may need to enhance any formula slightly to ensure that it becomes modified appropriately (or not) as it is used to fill ranges.
If it doesn't appear possible to do this, then you almost certainly have not designed your spreadsheet sensibly. A little time spent on design may well render the sort of editing you describe unnecessary.
In any case, why do you need to make such changes? If you need to insert additional rows or columns, your formulae should adjust themselves automatically to keep their functionality. Again, if not, you may need to insert your new rows or columns differently.
It is very likely that your real solution will be redesigning how your spreadsheet works, not tinkering with countless individual formulae.
I trust this helps. Brian Barker --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
