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.
On 22/09/18 12:58, email@example.com wrote:
=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
For the protection part:
Select the stuff which you wish to be UNPROTECTED (Columns A C G I) from
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
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. |
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
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.
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.
On Saturday, 22 September 2018 12:58:56 UTC+1, firstname.lastname@example.org 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.
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.
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
a Java engine (IIRC, a "JRE"). So the authors don't know that
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.
On 25/09/2018 12:28, email@example.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.
On 22/09/2018 23:12, firstname.lastname@example.org 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
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.
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.