uncle keith's thread on how to make checkable, updatable spreadsheets when it's getting crazy complicated and all you've got is a spreadsheet and four colanders 1/n (no not a joke)

1. Each sheet should have a clear function - if one sheet is serving many functions, split them out. In particular, for reasons extremely obvious to National right now, put your external input data - things that you expect to change - in their own sheets.

2. Having more than one set of column/rows within a sheet is sadly unavoidable. You're going to have many tables inside a sheet. But make your life easier by making them all the same size. How does this make life easier? Well...

3. You can use control rows/columns. These are cells that have one job - to calculate "which rows/columns should other cells point to"...

Why use control cells? Because using relative references (e.g. A1) that you change all the time WILL result in fuck-ups. You're going to be copy-pasting 50 things the night before your boss publicly launches it, and you're going to paste the wrong thing *somewhere*.

Relative references do weird - sometimes helpful - things, like automatically change if you cut the *target* cell, but not when you copy it. It *doesn't* change if you cut the source cell, but *does* change when you copy it. It's good logic, but my god does it introduce a lot..

of opportunities for things to not point to the thing you expect it to be pointing to. Worst of all - you can't see it. If you fucked up, you won't know.

Basically, you just never want your career to depend on your muscle-memory copy-paste having got it right 50 times in a row.

So, control cells, real basic: You have a cell that defines the sheet, one that defines the row, one that defines the column. Then in the cells, you can use INDIRECT(sheet, row, column) to access that cell.

That's it. That's the trick.

The example is real complicated, because it's a lookup in a range that's defined by the control cells.. it's actually a terrible example of "real basic". Soz.

This is better. It's just saying use sheet Data-IRD Tax Base, use column 4 (the "Taxable Income" column), and row 6+13 (to account for the extra header rows).

"But there's still relative references!" - Yes. But you only have to do it once, and then you never touch it again, and only ever change the highlighted cells, which change all the other ones.

e.g. If you wanted to add PREFU data, add a new PREFU sheet, make sure it's formatted the same as the BEFU sheet, then change the sheet cell reference from BEFU to PREFU - and it'll switch over smoothly sooo smoooothly mmmmmm

4. (yes that was a very long 3.) But that still leaves room for human error. So how do we mitigate? Error checking!

Here: Does a) the bunch of crazy spreadsheeting I've done above to break income into deciles add up to b) the total income as originally reported?

Very important of course to check those things *independently*, against the original data, rather than against things that you calculated (which would have the same mistakes).

Also great: eyeball-verifiable sanity checks. Here: How many people are there in each decile. If the numbers don't match, you fucked up. Easy!

Finally, an error check of error checks: It's no good having error checks 30 columns off the page that you never see, so have an overseeing error check that goes off if any of the error checks go off.

Use colours and conditional formatting to help you detect errors, and you can never have too many error checks.

Set up the system right. Don't rely on your dumb monkey fingers to push buttons correctly. Don't get your boss fired. Say no to failure.

P.S. Use font colours and hide-columns to keep the control elements almost invisible. This will help keep the data clean and visually comprehensible.