Gosh, openoffice is a sucky piece of software
I’ve been maintaining copies of cuesheets inside openoffice for the past couple of years, and today I found myself wanting to modify a cuesheet by adding some rows to the start and to the end of the thing (to extend the starting and ending locations of the offending route into NE Portland.)
So you’d think that it being a spreadsheet and all there’d be a good way to set up a formula on a row to add the distance from the cell one row one up and the leg distance from the current row to get the distance for the current row. Something like =IFNUM([-1,0]) && IFNUM([0,-1]) SUM([-1,0],[0,-1]) (if the cell one row up contains a number and the cell one column to the left contains a number then fill this cell with the sum of those two, otherwise put in nothing.)
But openoffice doesn’t have relative addressing. There are documents all over that say “oh, just use absolute cell numbers then cut and paste and they will magically change!” but, first, it doesn’t work, and second I’m not going to cut and paste a formula 250 times just to populate a line in a spreadsheet. And there are other documents that say “oh, addressing is all relative, so if you do =SUM(A1) that grabs the cell one up to the left, and =SUM(A2) grabs the cell just left of you but that doesn’t seem to work either.
So I find myself having to either (a) hand edit a g-dd-mn spreadsheet to get the numbers to work (something that does not reliably work; I did that for an alternative start to Portland-Ripplebrook-Portland a few months ago, and ended up with about a 4km difference between my edited cuesheet and the actual milage) or to just redo the entire route, then remake the spreadsheet from scratch.
Gosh, that’s certainly a labor-saving design. It’s almost as labor-saving as, oh, writing my own spreadsheet software to make cuesheets, and about as frustrating.
Comments
When I used your scheme I managed to get a sum formula that worked on a cell-by-cell basis (except that I had to cut and paste it all the way
down the table.) But unfortunately when I then went back and cut a row
out every single row under it went to #ERR (or something like that)
which was not exactly what I wanted to see.
So I went back into bike route toaster and replotted the route, then used openoffice calc in the traditional way; as a formatting tool to make the route look pretty.
Sigh.
Stupid openoffice.
Ah, yes. OpenOffice. The “Microsoft-compatible” suite which not only refused to open a spreadsheet our Beijing people sent, but also fell over spewing blood from every orifice. At least it stayed up when opening a Word document, so one could overlook the broken-unto-quasi-unintelligibility formatting.
Comments are closed
OpenOffice is the until-recently dead clutches-of-Oracle version. The first patch in 2+ years was very recent news.
LibreOffice (the “we’re taking our patches and going over here” version at http://www.libreoffice.org) is generally preferable. Maybe OpenOffice will take off as an Apache project, but right now, LibreOffice is a couple of years of work and “OK, we’re going to put in all the patches Sun didn’t want in the free version because they were selling a version that did that” ahead.
I may not be understanding what you’re trying to do, but
=IF(AND(ISNUMBER(N(A45)),ISNUMBER(N(B44))),SUM(B44,A45),‘’)
will do what I think you want, and barring any unfortunate quirks and foibles of the mac version, you can just keep pasting this down a column; copy it once, paste it many.
N() is a cast to number, so you don’t get an error for feeding emptiness to ISNUMBER.