Sheetoffset help needed

[bhbaldwin]

I am using the sheetoffset formula that has been written about previously on this site. I would like to input the value for a cell from the sheet next to it if it has been left blank on the original sheet.

Here is the code for the sheetoffset function:

Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function

The formula I am trying is:

=IF(ISBLANK(D2),sheetoffset(1,D2),D2)

This is only returning 0s for every cell. Ideally if D2 were blank on the current sheet it would input the value for D2 listed on the sheet to the right but if D2 were filled in it would retain the current value for D2. I am not sure where I am going wrong with this. Any help would be much appreciated.

 

[bhbaldwin]

I believe there may be a circular reference being created that is affecting the result but I am not sure of a way to resolve the problem.

 

[bhbaldwin]

If anyone is interested, the solution requires enabling iterative calculations through file --> options --> formulas --> enable iterative calculations