OT (semi) : Spreadsheets

Before this month, I have only used a spreadsheet for tabulating my books -- no calculation. [[[ Actually, I'd slightly like to total one column, that of prices - but that is variously in current pounds.pence, L/S/D., and sundry Foreign, so I think I'll not bother. ]]]
I now want to keep a simple account. I have a working sheet, in which the core is E8 +E8-G9, manually propagated down as far as is currently needed. Column A is Date, columns C E & G are Money. Other columns are text or empty.
So far, so good.
What I want is for the propagation of format and formula to *automatically* (A) go down as far as applicable, (B) apply to inserted lines; and, if possible, to protect everything except the values in columns A, C, G, I.
I am at present using LibreOffice, but answers for other spreadsheets may help.
Any suggestions?
--
(c) Dr. S. Lartius, UK. Gmail: dr.s.lartius@

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On 22/09/18 12:58, snipped-for-privacy@gmail.com wrote:

Enter this: =IF(COUNT(C8,E8,G9)<3,"",C9+E8-G9) into E9
This formula and any formatting can then be copied down as far as you want. Results in E9 will only appear when the conditions are met in the (COUNT(C8,E8,G9)<3 statement.
HTH
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On 22/09/18 13:24, Richard wrote:

For the protection part: Select the stuff which you wish to be UNPROTECTED (Columns A C G I) from the Menu Format > Cells > Cell Protection Tab > uncheck "Protected" > OK
Then from the Menu Tools > Protect Sheet > OK
To remove protection Tools > Protect Sheet
You will notice that the "Protect Sheet" in the Tools Menu has a tick when the sheet is protected.
If you want automation when inserting rows you will have to improve your skill level a bit and do macros as suggested by Jim
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On Saturday, 22 September 2018 13:47:57 UTC+1, Richard wrote:

It seems that, in combination, the wish to insert/extend and the wish to protect are incompatible with simplicity. Therefore I will (a) be extra careful when adding data (b) maintain in parallel my old plain-text method, in which nothing unexpected will happen apart from arithmetic errors

For that, I would need a working example of a similar macro, including details of how to make it run. I have, however, now found out where to edit a macro.
https://zapier.com/learn/google-sheets/ may have some macro examples; I've not yet got far enough to tell.
--
(c) John Stockton, near London, UK. Using Google Groups. |
Mail: J.R.""""""""@physics.org - or as Reply-To, if any. |
  Click to see the full signature.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

You can get a partial result by:
1) Put the formula into the cell in row 1 or 2 or wherever you start
2) Select that cell and shitloads more below by dragging with the shift key down
3) Select Fill Down from wherever it is prolly on the edit menu (that's where it is in Excel 2008, YMMV).
For inserted lines, formulae self-adjust automatically unless the reference in the cell has a $ with it, which forces that to be an absolute reference. So, you have put +E8-G9 in cell E9:
1) If you put $C9 then inserting/deleting can modify the 9 but not the C
2) If you put C$9 then inserting/deleting can modify the C but not the 9
3) If you put $C$9 then neither the C nor the 9 will change on any insert/delete where you would ordinarily expect them to.
--
If your experiment needs statistics, you ought to have done a better experiment.

Ernest Rutherford
  Click to see the full signature.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On 22/09/2018 19:06, Tim Streater wrote:

Or just /Edit/Copy from one cell and /Edit/Paste to the whole block; or Ctrl-C and Ctrl-V

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On Sat, 22 Sep 2018 04:58:53 -0700, dr.s.lartius wrote:

Not totally automatic, but very easy indeed, to get the propagation.
1) Set up the cells in the first row and select them.
2) Your selection will have a black blob at the bottom right corner. Position the cursor over that (it will change to a cross).
3) drag it down as many rows as you like - job done.
Inserted lines won't get the propagation, but just drag from the row above in the same way and it will replicate correctly.
--
My posts are my copyright and if @diy_forums or Home Owners' Hub
wish to copy them they can pay me £1 a message.
  Click to see the full signature.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On Saturday, 22 September 2018 12:58:56 UTC+1, snipped-for-privacy@gmail.com wrote:

I put *automatically* to show that I ***only*** wanted to hear of fully-automatic methods. That is because I am already doing it manually; I just don't want to have to remember to do it whenever it is needed.
--
(c) Dr. S. Lartius, UK. Gmail: dr.s.lartius@

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On Sat, 22 Sep 2018 15:12:17 -0700 (PDT), snipped-for-privacy@gmail.com wrote:

Not a direct answer but this site https://exceljet.net/ has a wealth of hints and tips for excel
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On 22/09/18 23:12, snipped-for-privacy@gmail.com wrote:

Use your google skills then.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

You didn't say that, however. Personally I'm not aware of any automatic methods, not having a mind-reading copy of a spreadsheet app.
--
When I saw how the European Union was developing, it was very obvious what they
had in mind was not democratic. In Britain you vote for a government so the
  Click to see the full signature.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On 23/09/18 09:50, Jim K wrote:

:)
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On 23/09/2018 10:55, Richard wrote:

You have specified a solution here rather than a problem.

One way to do this is to set a formula for the whole column which is conditional on there being a valid input in the dependencies column(s). (or a long enough piece of the column that you don't go past the end)
It avoids the need for macros and doesn't complicate things too much.
As a simple example for column B =IF(ISBLANK(A1),"",A1*A1) (and copy down the column)
Or you could do it by on Worksheet change macros but many users are not permitted or unwilling to run macros from untrusted sources.
--
Regards,
Martin Brown
  Click to see the full signature.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On Tuesday, 25 September 2018 09:12:14 UTC+1, Martin Brown wrote:

That is because I want a solution with that effect.

Well, I could set a formula near the top of the column and drag it ALL of the way down. I don't know whether a column of formulae is stored as a full array or as a sparse array.

By the way, if one wanders around the "Macros" sub-tree of the LibreOffice spreadsheet menus, one sees "Javascript"; I prefer JavaScript to Basic. But, on trying that, the software demanded a Java engine (IIRC, a "JRE"). So the authors don't know that JavaScript != Java, or they have a JavaScript engine written in Java, or ???.

Pragmatically, the solution seems to be that, after creating a new row, which will be empty, I should always copy-paste into it a row of the nearest-desired "type" and then edit in the new information, if any. There will always be a near row of the type, and often a near row with the right legend at the end. A visually-blank row is a month separator, but needs to contain a formula to propagate the running total.
I've not yet found a way to format a computed number cell to have the number invisible for both positive and negative values, but I can set the font size to 1. But I don't need invisibility, and am undecided as to whether it would be good to do it.
--
(c) Dr. S. Lartius, UK. Gmail: dr.s.lartius@ |

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On 25/09/2018 12:28, snipped-for-privacy@gmail.com wrote:

Two easy options for that. Hide the entire row/column or alternatively set the font colour to be the same as the background colour. Conditional formatting should be able to do this exactly leaving text or 0 visible.
--
Regards,
Martin Brown
  Click to see the full signature.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On 25/09/18 12:28, snipped-for-privacy@gmail.com wrote:

This may be of interest: https://wiki.openoffice.org/wiki/Documentation/DevGuide/Scripting/Writing_Macros

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On Tuesday, 25 September 2018 20:22:19 UTC+1, Richard wrote:

Yes, and also its neighbour pages; thanks.
--
(c) Dr. S. Lartius, UK. Gmail: dr.s.lartius@ |

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On 22/09/2018 23:12, snipped-for-privacy@gmail.com wrote:

Then you either have to learn how to write macros, or select and learn how to use a database. Which will take ages, and will change with new versions.
What most of us do is to learn the basics of Excel: copy, paste, paste special, relative and absolute addressing, named ranges, conditional formatting, and maybe pivot tables. With these you can quickly construct and modify a worksheet to do exactly what you need.
Actually Excel is sometimes too clever for its own good, with more recent versions you can get caught out when it does something to an inserted row or column that you were not expecting.
If you *know* that you are going to want a particular format to go all the way to the bottom of a column, then just define it at the outset.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
newshound wrote:

Indeed so. Conditional formatting can take a bit of sorting out after inserting rows.
Chris
--
Chris J Dixon Nottingham UK
snipped-for-privacy@cdixon.me.uk
  Click to see the full signature.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On 24/09/2018 16:34, Chris J Dixon wrote:

Couldn't think of an example, but I do remember getting caught out and that could well have been it. IIRC I was using conditional formatting to spot "interesting things" in large sets of real data.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

HomeOwnersHub.com is a website for homeowners and building and maintenance pros. It is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.