programming
Programming... it combines the most unpleasant aspects of: writing, engineering, and dealing with the worst kind of icky bureaucracy all into one nice, neat package.
I've not done much/any in-depth work with spreadsheets before. Well, who does really? I mean, if you can't point-and-click it in just a few moments, then why bother? Well I'm a masochist (read: programmer) so I wasn't gonna take no for a result (actually it was #!NAME).
I wanted a copy of my running total to always be displayed at a cell at the top of the worksheet. So that way I wouldn't have to go searching for it. I worked out that I could refer to the cell farthest down the sheet in that column with a number in it with this: "=COUNTIF($F$3:F65506;">0")+2".
Ok, so that's all neat and stuff. So now all I needed was to figure out how to dereference the number I had calculated to use as a cell reference. It shouldn't have been a big deal, but instead it took several agonizing hours to find the answer. I'm writing this down with the hope that I won't have to do this again. Maybe I should have tried that online google-for-code thing, ut I forgot it existed until just now.
Anyway, the magic to keep in mind is
1: Spreadsheets have an inanely simplistic syntax, so they handle all the sophisticated things (like dereferences) through functions.
2: Some people use the term "indirect" instead of "dereference". Or maybe indirection and dereferencing aren't quite the same thing. I forget, it's been a long time since assembly language.
Anyway, this is what it looks like:
"=INDIRECT(CONCATENATE("F";COUNTIF($F$3:F65506;">0")+2))"
yay
I've not done much/any in-depth work with spreadsheets before. Well, who does really? I mean, if you can't point-and-click it in just a few moments, then why bother? Well I'm a masochist (read: programmer) so I wasn't gonna take no for a result (actually it was #!NAME).
I wanted a copy of my running total to always be displayed at a cell at the top of the worksheet. So that way I wouldn't have to go searching for it. I worked out that I could refer to the cell farthest down the sheet in that column with a number in it with this: "=COUNTIF($F$3:F65506;">0")+2".
Ok, so that's all neat and stuff. So now all I needed was to figure out how to dereference the number I had calculated to use as a cell reference. It shouldn't have been a big deal, but instead it took several agonizing hours to find the answer. I'm writing this down with the hope that I won't have to do this again. Maybe I should have tried that online google-for-code thing, ut I forgot it existed until just now.
Anyway, the magic to keep in mind is
1: Spreadsheets have an inanely simplistic syntax, so they handle all the sophisticated things (like dereferences) through functions.
2: Some people use the term "indirect" instead of "dereference". Or maybe indirection and dereferencing aren't quite the same thing. I forget, it's been a long time since assembly language.
Anyway, this is what it looks like:
"=INDIRECT(CONCATENATE("F";COUNTIF($F$3:F65506;">0")+2))"
yay