This may be what you want.
A UDF (User Defined Function).

Put this in Module1:
Public Function Func1(rng1 As Range)
Func1 = Range(rng1.Formula).Offset(7, 0).Value
End Function ' Dave D-C

and this in D5:
=Func1(A5)

Stephen wrote:
Hi Folks,

I have a sheet which takes values from a second sheet on a weekly basis.
Sheet 1 has a cell with a reference to a particular cell in sheet 2. for
example

sheet1
A5 = sheet2 b7
D5 = sheet2 b14

is there a ay I can simply reference the reference foumula and add 7 or do i
have to hardcode each reference?

Hpoe this makes sense.

Thanks!