This will work inside a macro. Again, I need to use a worksheet function because we don't want to send excel files containing macros.

>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.
>>>>