Relative name range issue

[Craig]

I was looking at John Walkenbach's excel tips page
(http://j-walk.com/ss/). In one example, he shows how to
create a named range that is RELATIVE, not ABSOLUTE.

By clicking on, say, cell A1, then opening the named range
dialog from the menu (Insert\name\define...) and defining
a name such as "CellBelow" that refers to cell A2, you've
created a relative reference. Anytime you type
in "CellBelow" - in any cell - the value returned will be
the value of the cell directly below the cell you're on.

However, this appears to work only when you're on the
sheet that you were on when you created the name in the
first place.

For example, if I follow the above procedure and create
the named range on "Sheet1", then go to "Sheet9" and type
in "CellBelow", it still refers to "Sheet1". And when you
go into the defined named range dialog box, you will see
that Excel stamps 'Sheet1' before the cell reference - and
there doesn't appear that there's any way to change that.

Or is there? What am I not seeing here? Is there a way
around this? I thought the beauty of named ranges is that
they can be used anywhere in a workbook.

Thanks for any and all help.

 

[Earl Kiosterud]

Craig,

Manually remove the sheet name from the "refers to." Instead of
=Sheet2!B2, make it:
=B2

Note that the F4 key will toggle the various relative/absolute combinations,
if the cursor is on or near the B2.

 

[Craig]

That DOESN'T WORK.

You CAN'T REMOVE the 'Sheet1' before the cell reference.

Is there a way around this???

-----Original Message-----
Craig,

Manually remove the sheet name from the "refers to." Instead of
=Sheet2!B2, make it:
=B2

Note that the F4 key will toggle the various

relative/absolute combinations,

 

[Frank Kabel]

Hi
try manually entering
=!B2

note the exclamation mark at the beginning

 

[Dave Peterson]

Try this variation in your "refers to" for cellbelow:

=INDIRECT("r[1]c",FALSE)

 

[Craig]

Thanks for all the input. Both work great...