Formula: Table1[[#This Row],[Column1]] BUT need Previous Row

I'm creating formula's for some rows in an excel table (eg: Table1)
& in the formula, I need to be able to reference the previous row & next row but cant figure out the syntax. There's a [#This Row] special item specifier but there's nothing like [#Previous Row] or [#Next Row].

Eg: a formula in Column5 may need to access the current row in Column1 AND the previous row in Column1...

Current row:

=Table1[[#This Row],[Column1]]
Previous & next row: wrong syntax - FAILS

=Table1[[#This Row] - 1,[Column1]]
=Table1[[#This Row],[Column1] - 1]
=Table1[[#This Row] + 1,[Column1]]
=Table1[[#This Row],[Column1] + 1]


If I manually type in the formula in say D2 & use formula of =A2-A1, it shows this, but I need to write it dynamically in VB, such that if the columns are moved later it will still work, dont want hardcoded value of say A1:

D2 formula, BUT with hard code:

=A2 - A1 - A3 ' dont want this
=Table1[[#This Row],[Column1]] - A1 - A3


Any ideas please?

I've got it... woohoo... spent a while on this... :)

An Excel table called Table1 (Add Table Style with Totals row):

Row1...Column1...Column2...Column3...Column4
Row2.......1...........11...........21...........-1
Row3.......2...........12...........22...........-2
Row4.......3...........13...........23...........1
Row5....Total......... ............ ...........-2

current row - previous row - next row
D2 Formula =A2-A1-A3 =1-"Column1"-2 = #VALUE
D2 Formula =A2-IF(ISNUMBER(A1),A1,0) - IF(ISNUMBER(A3),A3,0) =1-0-2 =-1
(fills the entire D column)

coded as either:

'hard coded relative
Cells(2,4).FormulaR1C1 = "=RC[-3] - IF(ISNUMBER(R[-1]C[-3]),R[-1]C[-3],0) - IF(ISNUMBER(R[+1]C[-3]),R[+1]C[-3],0)"

'hard coded actual
Cells(2,4).FormulaR1C1 = "=RC1 - IF(ISNUMBER(R1C1),R1C1,0) - IF(ISNUMBER(R3C1),R3C1,0)"

'hard coded combination of relative & actual
Cells(2,4).FormulaR1C1 = "=RC1 - ISNUMBER(R[-1]C1) - ISNUMBER(R[+1]C1)"

'dynamic
Dim col as Integer
col = Range("Table1[Column1]").Column ' col = 1

Cells(2,4).FormulaR1C1 = "=RC - IF(ISNUMBER(R[-1]C" & col & ",R[-1]C" & col & ",0) - IF(ISNUMBER(R[+1]C" & col & ",R[+1]C" & col & ",0)"
' or
Cells(2,4).FormulaR1C1 = "=Table1[[#This Row],[Column1]] - IF(ISNUMBER(R[-1]C" & col & "),R[-1]C" & col &",0) - IF(ISNUMBER(R[+1]C" & col & "),R[+1]C" & col & ",0)"

or could use the OFFSET function... (thanks Rorya on another forum)


Cells(2,4).FormulaR1C1 = _
"=Table1[[#This Row],[Column1]] " _
"- IF( ISNUMBER( OFFSET(Table1[[#This Row],[Column1]],-1,0) ), OFFSET(Table1[[#This Row],[Column1]],-1,0), 0) " _
"- IF( ISNUMBER( OFFSET(Table1[[#This Row],[Column1]],+1,0) ), OFFSET(Table1[[#This Row],[Column1]],+1,0), 0) "