Hi Chuck:

In cell A2, instead of:

A100

use:

=ADDRESS(ROW(A100),COLUMN(A100))

Regards,

Vasant.

"Chuck Buker" wrote in message
...

Cell A2 contains the text "a100". Cells A5
through F15 contain indirect and offset functions that use
the text in A2 to cause cells A5 through F15 to refer to
cells near cell A100. For example, cell A5 contains the
formula: =offset(indirect($a$2),2,4). This causes cell
A5 to display the contents of cell E102.

I can instantly change what is displayed in each
of the 66 cells A5 through F15 simply by changing the text
in cell A2.

Unfortunately, using this example, if I 'move'
cell A100 or insert some number of rows into the
spreadsheet above cell A100, cell A5 continues to display
the contents of cell E102 even though the original
contents of cell A100 and, if I have moved E102 or
inserted rows above A100, the original contents of cell
E102 are now somewhere else. This is because the
text "a100" in cell A2 did not change when I moved cell
A100.

If cell A2, instead of containing text, contained
a cell reference (e.g., +a100), and another cell, say A3,
contained an Excel function that converted the cell
reference in A2 to text (e.g., from +a100 to "a100"), then
I could point my offset/indirect function to cell A3
[e.g., =offset(indirect($a$3),2,4)], and the reference
would effectively follow cell A100 as it was moved around
the spreadsheet. The function I am looking for is alomst
the opposite of the 'indirect' function - instead of
converting text to a cell reference, it converts a cell
reference to text.

How can I accomplish this?

Thanks,

Chuck