I am generating an xml file in excel format and need to generate formulas
which will refer to cells on the current row.
Using R1C1 format you can put a formula like

=RC[-4]

to refer to the 4th column to the left of the cell with the formula. In
A1B1 format there is no equivalent (that I know of).

Does anyone know of a way of achieving the same when you know neither the
current cell reference nor row number? The offset function will not work
because I don't have the current cell reference.

One solution would be if there were function which returns the current cell
reference, so that the following would work

=offset (myreference(), 0,-4,1,1)

but I cannot find a myreference() type function. Row() and column() exist
to return the current row and col, but not the two together.

aaagh, a solution has to exist, but I can't find it.

Ian

Just a thought, but you could try saying something like, if row() and
col() = row() and col(), then offset, otherwise nothing.

Essentially it will always be true and therefore offset from the
current position.

Ian Murphy wrote:
I am generating an xml file in excel format and need to generate formulas
which will refer to cells on the current row.
Using R1C1 format you can put a formula like

=RC[-4]

to refer to the 4th column to the left of the cell with the formula. In
A1B1 format there is no equivalent (that I know of).

Does anyone know of a way of achieving the same when you know neither the
current cell reference nor row number? The offset function will not work
because I don't have the current cell reference.

One solution would be if there were function which returns the current cell
reference, so that the following would work

=offset (myreference(), 0,-4,1,1)

but I cannot find a myreference() type function. Row() and column() exist
to return the current row and col, but not the two together.

aaagh, a solution has to exist, but I can't find it.

Ian

Sorry, I don't understand what you mean. How would a formula like

= iif (row() and col() = row() and col(), offset(......

do anything, you are still stuck with the offset() requiring a cell
reference, which is lacking.

Ian Murphy