Maybe this? It takes value from the cell on the very same row, previous column,and add 1:

ActiveCell.FormulaR1C1 = "=RC[-1]+1"

>I need to grab the data from a cell based on the current row but I can't use a macro. It has to be a worksheet formula.
>
>>Is it what you need?
>>
>>ActiveCell.Offset(rowOffset, columnOffset).Activate
>>
>>>Hi all,
>>>
>>>I posted a similar question to an Office Dev forum and got no reply. I guess nobody was home.
>>>
>>>Anyway, this is an excel question but I was hoping someone knows how to do this. How do I automatically update a value based on the current cell without using macro.
>>>

>>>Example:
>>>  A1 (R1C1) = 100 && active cell
>>>  B1 (R2C1) = 200
>>>
>>>Formula in E1
>>> = offset(currentRow,0,0,1) && how do I get currentRow to be equal to the activecell row.
>>>