Named range self-referencing offset

Hi all,

I'm trying to figure out how to create a formula for a named range that returns a value a constant offset from wherever the named range is called.

Assume a worksheet with the following data:

   A  B  C
1 25 Bob
2 42 Sue
3 99 Joe
4

I'm looking to make a named range that returns an offset value using the cell in which the range is called as the offset function's reference.

For example, if my offset is [rows]0 [columns]-1, and my range is named 'OffsetReturn', when I put the formula =OffsetReturn in cell C1 I'll get the value Bob. In C2 I'll get Sue and in C3 I'll get Joe.

If my offset is [rows]1 [columns]-2, when I put the formula =OffsetReturn in cell C1 I'll get the value 42, in cell C2 I'll get 99 and in cell C3 I'll get 0 (or an error, since there's no data in A4 to be returned by OffsetReturn).

I've tried this formula in a named range: =offset(indirect(cell("address")),0,-1) but the cell function is volatile and is based on whatever the active cell is when the book re-calculates (usually on change).

Anyone have any ideas? For my context I can't use VBA, it's gotta be a function (that won't trigger security errors).

I'd appreciate any help!

Thanks,
Keith