Issue with local named relative range

[tbone]

Thanks to the Excel wizards here, I got some great tips back in
November with creating a way for a cell to refer to itself (e.g. THIS)
or the cell above it in a formula. If the active cell is A2, I can
define a name. e.g. CellAbove, that refers to A1 (no dollar signs =
relative reference). This was a pleasant surprise for me, as I had not
realized nor ever tried to create a named relative range.

However, I've now come across an issue with this technique and I hope
the wizards will assist once again.

It seems that Excel (2003 at least) always places the sheet name in
the reference. If I manually delete the sheet name in the Define Name
dialog's Refers To box, and click Add, the sheet name is restored. I
also found that defining the name via code yields the same result.

I think this means that a named relative range such as "THIS" can only
work for one sheet in a workbook - *unless* we can define a local
(i.e. per sheet) named relative range rather than a global one.

Unfortunately, I have not been able to convince Excel to do this. It
seems to be impossible using the Name Manager add-in (local relative
names simply disappear), and my fiddling with Define Name hasn't
revealed a way.

Any suggestions for a workbook-wide technique, or at least a
per-worksheet one, besides having to manage different names per sheet
(e.g. THIS1, THIS2, etc)?

I really like the power and simplicity of the named relative range; I
hope there's an equally concise way to have local ones too.

Thanks
tbone

 

[Dave Peterson]

I created a name: CellAbove
And used this in the refers to box: =INDIRECT("r[-1]c",FALSE)

R[-1]C
is the R1C1 reference style to refer to row above (-1) and the same column. The
false tells the =indirect() function that you're passing a string using an R1C1
reference.

 

[tbone]

Excellent! Works great!

Thanks!
tbone