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:

Please Login or Register  to view this content.

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

On Sheet1, select C1 and do Insert > Name > Define Sheet1!relLeft refers to: Sheet1!B1 (no $ signs).

Enter =relLeft in in any cell on Sheet1 and it will return the value of the cell to the left.

hi Keith,

Welcome to the Forum

What about using the oldie, but still, a goody, of the Vlookup function?
It would remove the volatility of using offset but still allow some flexibility to change your returned column.

I've taken the liberty of suggesting a modified Layout of:
row 1 for the sheet header,
row 2 for parameters/subtotals etc
row 3 for column headers
row 4 as the first data row (the "original row 1")

Then entering the below formulae...
[code]cell C2 = what ever column you want returned (as a number)
cell C4 (& copied down) =VLOOKUP($A4,$A$4:$B$6,$C$2,0) [code]

If you wanted to be slightly fancier, you could use data validation to build a list of column headers to choose from which gives a dropdown in cell C2, then replace the "$C$2" in the vlookup with a Match function* that converts the selected text from the C2 dropdown into a column number.
*Personally, I would put the Match function in a separate cell (such as c1 or swap so it's in c2 (with the list in C1)) or in a named range and then refer to this cell/named range, this approach means it would only be calculated once instead of once for every cell with the vlookup formula.

Of course the above approach could also be modified to work within in named range like Shg's suggestion...

hth
Rob

I didn't realise that you could use relative reference in named ranges

I use a bunch of standard names ranges in my template sheets Book.xlt and Sheet.xlt. Here are two:

Me (refers to the cell in which the reference appears). Suppose you have a named range tbl that starts in A2, and you want to show the sum of the column where the formula appears (the formula is not in the table). Then you can use this draggable formula: =INDEX(tbl, 0, COLUMNS($A$2:Me))

relAbv refers to the cell above, so you can do =SUM(A1:relAbv). You can insert rows immediately above the formula and they will always be captured in the sum.

hi Keith,

The FAQ section mentions how to mark threads as Solved (http://www.excelforum.com/faq.php).

Shg's got you sorted for your current issue but if you are interested in learning an alternative to Vlookup, have a read of the below link on Charles' site, esp the section titled "VLOOKUP versus INDEX and MATCH or OFFSET".
http://www.decisionmodels.com/optspeede.htm
(in fact, I recommend reading the whole site!)

These pages may explain the Index/Match concept more clearly: http://www.mrexcel.com/articles/exce...ndex-match.php or http://www.ozgrid.com/Excel/left-lookup.htm

Rob

Hi again!

Thanks shg, some excellent suggestions. I'll play with these over the next few sheets I deal with.

Thanks also broro183, I'm already familiar with the index/match combo and using offset for lookups, very handy in certain circumstances.

Thanks again, very very much appreciated!
Keith