UDF for pulling value from previous worksheet

[bltnwp]

Hello,

I'm a first time poster long time forum diver. I am trying to write a custom function that will let me get a cell value from X many worksheets back. Something like =Functionname("X","N") where X = how many worksheets to go back and N = the cell address from which to pull the value. I have pretty minimal programming knowledge and have no idea where to start on this one. I usually look for code that gets close to what I need and manipulate it from there but can't find anything like this.........Anyone willing to take a stab at it? I am sure I will /facepalm due to it's simplicity.............

Thanks,

[DonkeyOte]

Questions:

what happens if the offset goes back beyond sheet1 (eg to non-existent sheet) - should this then revert to last sheet and work back from there ?

what happens if there are hidden sheets or chart sheets ?
should these be ignored in terms of the offset ?

The morefunc.xll add-in has a SHEETOFFSET function but it will be fairly straightforward to create your own UDF along similar lines - but the above should be outlined first.

[shg]

Here's one:

Function OtherSheet(ByVal vRng As Variant, _
                    Optional ByVal iIndex As Long = -1, _
                    Optional bRelative As Boolean = True) As Range
    ' shg 2010
    
    ' Returns Range(vRng) on
    '   Worksheets(iIndex)                                      if bRelative = False
    '   Worksheets(iIndex + Application.Caller.Worksheet.Index) if bRelative = True
    
    ' Defaults to the range on Application.Caller.Worksheet.Next
    
    ' If the referenced range doesn't overlap the calling range, just pass the range:
    '   =SUM(OtherSheet(A1:A3))
    
    ' If they do, pass the range reference in quotes to avoid a circular reference error:
    '   =SUM(OtherSheet("A1:A3"))
    
    ' To pass multi-area ranges, enclose in parens or quotes:
    '   =SUM(OtherSheet((A1:A10, C1:C5), 2, False))
    
    ' No error checking -- it's a UDF _only_
    
    Application.Volatile True
    If TypeOf vRng Is Range Then vRng = vRng.Address
    
    With Application.Caller.Worksheet
        If bRelative Then iIndex = .Index + iIndex
        Set OtherSheet = .Parent.Worksheets(iIndex).Range(vRng)
    End With
End Function

[bltnwp]

shg,

That UDF is perfect and so versitle! Thanks much for the help.