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

[shell_l_d]

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 but cant figure out the syntax. There's a [#This Row] special item specifier but there's nothing like [#Previous 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 row: wrong syntax - FAILS

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

Any ideas please?

 

[shell_l_d]

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

An Excel table called Table1:

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

D2 Formula =A2-A1-A3 =2-1-3 =-2
coded as either:

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

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

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

'dynamic 
Dim col as Integer
col = Range("Table1[Column1]").Column     ' col = 1
Cells(2,4).FormulaR1C1 = "=RC" & col & " - R[-1]C" & col & " - R[+1]C" & col

 

[RoryA]

You could also use Offset:
=OFFSET(Table1[[#This Row],[Column1]],-1,0)
for example.

 

[shell_l_d]

Sorry my 2nd post (solutions) should have read 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)"

 

[shell_l_d]

Thanks Rorya... OFFSET is exactly what I was needing.
Which gives this:

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) "