So im trying to make a function that uses this formula:
H6=I6-(SUM(C6:G6) but instead of I6 C6 and G6 and H6 a named variable (ill call Ref)
so Id like something like this...

Range(ref).Offset(0,1)-(SUM(ref.Offset(0,-5) : ref.Offset(0,-1)
The first part is easy

"=" & Ref.Offset(0, 1)
however I dont know how to figure out the next part because ref.offset(0,-5) returns the value rather then the reference as a cell

Can anyone help?