=OFFSET question

A

Art

Hi All,

My first posting so thanks in advance for any help

I have the following formula in J12
='Summary-March2006'!$C$16

I tried the following but no go
=OFFSET(J12,-7,2)

Yet when I translate the formula into a literal string then it works
well as follows
=OFFSET('Summary-March2006'!$C$16,-7,2)

What function can i use to convert the reference argument of the OFFSET
function into text or string ?

Thanking you kindly !

Regards
 
D

Dave Peterson

I think the function you want to use is =indirect(). But that expects to see
something that looks like an address--and you're bringing back the value in that
cell--not the address of the cell.

If you had Summary-March2006 in a different cell, you could use:

=indirect("'" & x999 & "'!c16)
in J12
and
=OFFSET(indirect("'" & x999 & "'!c16)),-7,2)
in your other cell

But there's nothing built into excel that will allow you to extract that address
from your formula.
 
E

Ed Ferrero

Hi Art

You could use a user defined function (UDF);
Paste this in the Visual Basic Editor

Function GetFormula(r As Range) As String
Dim tmp As String
tmp = r.Formula
GetFormula = Right(tmp, Len(tmp) - 1)
End Function

Then enter in J13
=GetFormula(J12)

And use
=OFFSET(INDIRECT(J13),-7,2)

To get the value you want.

However, UDFs do slow things down.

I prefer to enter the row no, column no, and sheet ref in cells, then use
ADDRESS and INDIRECT to refer to cells dynamically.

For example;
Cell Contents
A1 16
A2 3
A3 Summary-March2006
A4 =ADDRESS(A1,A2,1,1,A3)
A5 =INDIRECT(A4)
Same as ='Summary-March2006'!$C$16
A6 =INDIRECT(ADDRESS(A1-7,A2+2,1,1,A3)
Same as OFFSET formula above


Ed Ferrero
http://www.edferrero.com
 
A

Art

Thanks Guys,

I have used Ed's UDF as it seems best in dealing with sheet name
changes by the user.

Many thanks for all the useful suggestions

Regards

Art
 
Top