Spreadsheet Programming, Blech! But how to do it?

I need to make a speadsheet program in order to help a friend who uses spreadsheets. It's easiest for me to try an Open Office "Calc" spreadsheet since I use Linux. But I can't find a good explanation of spreadsheet programming in terms that relate to an ordinary computer language like C, Basic, Java etc.

For example, take the question of "order of operations". If someone changes a cell in a spreadsheet, I assume they can tell the spreadsheet to update itself. But in what order would the updates of cells be performed. The value in a cell may depend on values in other cells. So the order of updating them could matter.

As another example, take the conceptually simple task of wanting N rows of data for month 1,2,..N where N is a number that the user of the spreadsheet may change. It's clear how to print out N rows of data in an ordinary programming language with the rows beginning with the number of the month. But what is the way to do this in a spreadsheet?

Spreadsheets are not a programming language. Period.

Order of operations is easy. If the value of a cell is unknown, then proceed to calculating other cells, then go back and try again and so on.

You don't tell a spreadsheet to print N rows of data. You can tell a macro to do so, though. However, the VBA used in OO may or may not be compatible with VBA used in MSoffice.

Got anything more specifies to ask?

Yes!

Is there a function that can be used to obtain the row and column number of the cell where the function is being executed? Can a function be written that refers to cells whose row and column are computed with variables?

The reason I ask:

A primitive idea for the first cell in N rows of monthly data would be to have a function that says:
This cell's value is the previous cell's value plus 1 when that value is less than or equal to N.
Otherwise this cell's value is 999 (or some value indicating "Not Applicable").

I could copy that function into the first cell of, say, row 2,3,...100, assuming my friend would never need more than 100 rows.

However, the function makes reference to the value in "the previous cell". The index of that row would be the row index of the current cell minus 1. But is there a function that returns "the row index of the current cell"?

From reading elementary tutorials, it isn't clear to me whether one can use a variable as the index of a row. I can have "A1", but can I have "AX" or "A[X]" or something like that? I haven't found any syntax that Open Office Calc likes.

Yes, there is a 'function' that returns the row index, but you do not need to use the scripting features to use it. It's one of the formula functions. To get the row index of a cell use: CELL("ROW";X) where X is the cell reference.

Although, from reading your example problem you could use something like:IF(A1<$F$1;A1+1;999) where the list of numbers is in the A column and $F$1 is the constant cell reference to the cell that holds 'N'. This formula would be entered in every cell AFTER the first.

Also, as far as I know Calc does not use VBA, it has it's own BASIC, Javascript, Python and something called Beanshell. Scripting is most useful for either very complicated calculations or very repetitive actions.

Calc can do some limited functions that compatible with MSO. Tried a few month ago..