relative cell naming

[rudyp]

I use <Ctrl>g to navigate to named cells in my workbook. It works fine except when I have named a cell and then sort in some new rows above the row where I have the named cell. When I name a cell, it seems that absolute is the only choice. I need it to refer to a relative cell, e.g. Sheet1!B12 instead of $B$12. Actually, $B12 would be fine. I want the cell in the same column but stays with the row when I sort the rows. Does this make sense?

When I remove the absolute $, the <goto> does not work.

How can I name a cell that stays with the row when I sort rows?

[shg]

Welcome to the forum, rudy.

Select some cell (say, A1). and do Insert > Name > Define, colB, refers to =$B1

Now colB always refers to column B in the same row in which the reference appears.

[rudyp]

First, thanks for such a prompt response.

I tried that before ($B12) and just did again. When I use the GoTo navigation, it takes me to the column but not to the row. e.g. B1 not B12 which is the one I named. Also the name does not appear in the address box or whatever it's called up there next to the function window.

With Excel2007 I am using the old transitional navigation option in this spreadsheet (from 1-2-3 [I started this workbook in the mid-80s.]) Would that make a difference? It works just fine for any that are absolute.

[shg]

If you're on row 3, it will take you to B3; if you're on row 27, it will take you to B27, ...

[rudyp]

But that doesn't solve navigating to the cell from another worksheet in the workbook. For instance I am on sheet1 and I want to go to a cell on another worksheet: <name> sheet2!B12 (with neither column or row absolute.) I can't make it work either with the Name Box or <Ctrl>g.

[shg]

Perhaps you can post a workbook and explain exactly what you want to do in context.