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:

[hidden email]
For additional commands, e-mail:

[hidden email]