Syntax for a relative formula
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 |
Syntax for a relative formula
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 |
Syntax for a relative formula
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 |
All times are GMT +1. The time now is 04:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com