Referencing a referenced cell

[bryanfox]

Sheet1!C4 contains the formula =Sheet2!A1

I want Sheet1!D4 to find out what cell Sheet1!C4 is referencing an
then return the value two columns over. (Offset will take care o
returning the value two columns over). My problem is I need to kno
which cell Sheet1!C$ is referencing. If I use the formul
"=OFFSET(Sheet1!C4,0,1)" I get the value from one column to the righ
of Sheet1!C4, I need the value of one column to the right of the cel
being referenced by Sheet1!C4, (Sheet2!A1) How would I do this.

 

[Frank Kabel]

Hi
see your post in Excel.misc

 

[JE McGimpsey]

If C4 will only contain a reference, you can use this UDF:

Public Function OffsetRef(rRange As Range, _
Optional nRows As Long = 0, _
Optional nCols As Long = 0) As Variant
Dim rBase As Range
Dim bValidRef As Boolean

If rRange.Count = 1 And rRange.HasFormula Then
On Error Resume Next
Set rBase = Range(Mid(rRange.Formula, 2))
On Error GoTo 0
If Not rBase Is Nothing Then
With rBase
bValidRef = ((.Row + nRows) <= .Parent.Rows.Count) And _
((.Row + nRows) > 0) And _
((.Column + nCols) <= .Parent.Columns.Count) And _
((.Column + nCols) > 0)
If bValidRef Then OffsetRef = .Offset(nRows, nCols)
End With
End If
End If
If Not bValidRef Then OffsetRef = CVErr(xlErrRef)
End Function


Call as

=OffsetRef(C4,0,2)

If you're not familiar with UDFs, take a look at David McRitchie's
"Getting Started with Macros and User Defined Functions":

http://www.mvps.org/dmcritchie/excel/getstarted.htm