OT (semi) : Spreadsheets

[dr.s.lartius]

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 =C9+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?

[Richard]

Enter this: =IF(COUNT(C8,E8,G9)

[Richard]

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

[Tim Streater]

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 =C9+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.

[newshound]

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

[Bob Eager]

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.

[dr.s.lartius]

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.

[Phil Addison]

Not a direct answer but this site

has a wealth of hints and tips for excel

[Richard]

Use your google skills then.

[Tim Streater]

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.

[Richard]

:)

[dr.s.lartius]

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.

may have some macro examples; I've not yet got far enough to tell.

[newshound]

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.

[Chris J Dixon]

Indeed so. Conditional formatting can take a bit of sorting out after inserting rows.

Chris

[newshound]

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.

[Martin Brown]

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.

[dr.s.lartius]

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.

[Martin Brown]

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.

[Richard]

This may be of interest:

[dr.s.lartius]

Yes, and also its neighbour pages; thanks.