Ask Your Question

PaulHaz's profile - activity

2019-12-06 18:13:24 +0200 marked best answer Use array formula to calculate balance

I have several spreadsheets dealing with money. They all have (at least) the same three columns: Amount In, Amount Out and Balance. If the are columns A, B and C, the columns headings are in row 1 and the opening balance is in C2, then C3 contains "=C2+A3-B3". I can copy that down as far as I need, and it all works fine. Mostly.

If someone enters a value in the wrong column and then moves it, the formula calculating the balance on that row also changes, which is not what I want to happen. It changes even if I have protected the sheet and hence the balance column, which I think is arguably a bug - it really ought to ask if that is what I want it to do.

The obvious way to make a repeating formula unchangeable is to turn it into an array, something like {=C2:C102+A3:A103-B3:B103}, but that doesn't work either as this gives me a 522 Error because most of the column C references are inside the array. Even though every instantiation of the array has no circular reference, the array as a whole thinks it does.

Firstly, is this a bug which can be fixed? The array ought to be shorthand for a series of identical calculations, and all the calculations, either individually or taken together, are valid, so the array ought to be valid.

Secondly, is there a workaround? This feels like a very common situation which should have been resolved in some way long ago, but maybe I'm searching for the wrong thing?

The other benefit of making this an array, or course, is that the spreadsheet ought to be smaller and, I assume, more efficient. But my primary concern right now is to stop people accidentally screwing it up. Telling people they are not allowed to cut and paste a number from the wrong place to the right place really isn't an answer!

2019-12-06 18:13:24 +0200 received badge  Scholar (source)
2019-11-06 11:04:59 +0200 received badge  Famous Question (source)
2019-07-15 23:48:15 +0200 received badge  Notable Question (source)
2019-07-15 23:30:44 +0200 commented answer How do I identify a substituted font?

Thank you for the background - very helpful. I recognize the necessity of font substitution. I can see that the decisi

2019-07-15 23:21:14 +0200 commented question How do I identify a substituted font?

I should add that I generally create documents in Writer then generate a PDF and circulate that so I can have some contr

2019-07-15 23:14:35 +0200 answered a question Use array formula to calculate balance

The suggestion to use a named expression solves my problem elegantly, so thank you!

2019-07-15 23:14:27 +0200 commented answer Use array formula to calculate balance

I'm happy to argue my point about how arrays ought to function, but that should be done elsewhere. I'm also happy to ar

2019-07-15 23:14:27 +0200 received badge  Commentator
2019-07-15 22:59:48 +0200 commented question Use array formula to calculate balance

Thanks for the comment, but the use case is one where the User repeatedly adds new entries, and needs to know the balanc

2019-07-15 16:12:53 +0200 received badge  Popular Question (source)
2019-07-14 02:16:40 +0200 answered a question In a LibreOffice Writer document how can I number the pages automatically?

Create a page header or footer and insert the page number as a field (In the version I have, it is: Insert -> Field -

2019-07-14 00:13:32 +0200 received badge  Supporter (source)
2019-07-14 00:10:28 +0200 answered a question How to compare data across calc worksheets

If you compare two cells, it is the values of those cells being compared and not the formatting. Assuming the data is i

2019-07-14 00:00:14 +0200 received badge  Enthusiast
2019-07-13 23:50:54 +0200 asked a question Use array formula to calculate balance

use array formula to calculate balance I have several spreadsheets dealing with money. They all have (at least) the same

2018-12-16 00:01:14 +0200 received badge  Famous Question (source)
2018-12-16 00:01:14 +0200 received badge  Notable Question (source)
2018-07-21 15:55:44 +0200 received badge  Famous Question (source)
2018-04-04 15:19:39 +0200 received badge  Famous Question (source)
2018-04-04 10:58:27 +0200 received badge  Popular Question (source)
2018-04-03 23:59:23 +0200 asked a question How do I identify a substituted font?

How do I identify a substituted font? I have some Writer documents which were created on another machine. I have printed

2018-04-03 22:36:13 +0200 edited question Is there a way to create a non-alphabetic index?

Is there a way to create a non-alphabetic index? I want to create an index of Biblical references. A Biblical index is

2018-04-03 22:34:00 +0200 edited question Is there a way to create a non-alphabetic index?

Is there a way to create a non-alphabetic index? I want to create an index of Biblical references. A Biblical index is

2018-04-03 22:34:00 +0200 received badge  Editor (source)
2018-04-03 22:19:47 +0200 commented answer Is there a way to create an index of Biblical references?

Update: I struggled to make this precise solution work, partly because I sometimes mis-typed the entry and could not fin

2018-02-23 15:08:20 +0200 received badge  Notable Question (source)
2018-02-09 13:54:11 +0200 commented question Is there a way to create an index of Biblical references?

That would be appreciated. Thank you.

2018-02-09 13:46:45 +0200 edited question Is there a way to create an index of Biblical references?

Is there a way to create an index of Biblical references? I want to create an index of Biblical references. I can tag th

2018-02-09 02:34:44 +0200 commented answer Is there a way to create an index of Biblical references?

Ah! Thank you. I missed that detail. I assumed the index entry would be the same as the selected text, but on reflecti

2018-02-08 03:07:10 +0200 commented answer Is there a way to create an index of Biblical references?

That is neat. I must confess I am reluctant to write all the references in the text in the form "Genesis 01:07" - I thin

2018-02-08 03:05:41 +0200 received badge  Nice Question (source)
2018-02-07 14:46:44 +0200 received badge  Popular Question (source)
2018-02-07 12:39:23 +0200 asked a question Is there a way to create a non-alphabetic index?

Is there a way to create a non-alphabetic index? I want to create an index of Biblical references. A Biblical index is

2018-02-06 11:58:21 +0200 received badge  Notable Question (source)
2018-02-05 22:43:13 +0200 received badge  Popular Question (source)
2018-02-05 21:45:59 +0200 received badge  Student (source)
2018-02-05 17:43:28 +0200 asked a question Is there a way to create an index of Biblical references?

Is there a way to create an index of Biblical references? I want to create an index of Biblical references. I can tag th

2017-11-24 17:15:21 +0200 commented question Font Substitution

I'm using LibreOffice on Ubuntu, and I like the font being substituted in some documents on my machine - would like to u

2017-02-28 10:32:57 +0200 commented answer Permanently set default page style

Yes, it is counter-intuitive in many places. And the Template management area is particularly confusing. Apart from the interface issues, you can put a new Template file into the Templates directory and it does nt appear on your list of available Templates ...

2017-02-21 22:49:05 +0200 commented answer Permanently set default page style

This is a fair point: the Default Page style is a misleading name for a particular page style in the current document, and changing the current document does not change the default template - but this is not obvious to many people, and LO does not make it clear. The whole Template management area is a complete mess with obscure functionality grouped in odd ways - which does not encourage people to explore and learn. And it is not clear how to contribute and make it better in this area.

2017-02-21 22:41:39 +0200 commented question Permanently set default page style

I agree that the LO interface is often confusing - and I do not say that because I like the M$ way, because I loath that even more, most of the time. I have used a great many text editors and word processors over the years, and trained others in their use. Some were clearly easier to use with a more intuitive interface than others: LO is neither the best nor the worst I have experienced. And I still miss some of the functionality I used to take for granted in WordPerfect.