How do I find the contents of a cell using the "ADDRESS" function.
 

So, I am trying to use a number stored in a cell to look that number of cells
up, down, right, or left of a different cell. I have figured out how to get
the address of the new cell using the ADDRESS function. For example:
=ADDRESS(ROW(A1)+5, COLUMN(A1)). This will look 5 cells down from A1.
However, this just returns the address of the cell. That equation would
return "A6." What funciton can I nest ADDRESS in to give me the contents of
this new cell?

"sweeney" wrote...
So, I am trying to use a number stored in a cell to look that number of
cells up, down, right, or left of a different cell. I have figured out
how to get the address of the new cell using the ADDRESS function. For
example:
=ADDRESS(ROW(A1)+5, COLUMN(A1)).
This will look 5 cells down from A1. However, this just returns the
address of the cell. That equation would return "A6." What funciton
can I nest ADDRESS in to give me the contents of this new cell?

While you could use the ADDRESS call as the argument to INDIRECT, a better
approach would be

=OFFSET($A$1,ROW(A1)+5-1,COLUMN(A1)-1)

Since OFFSET and INDIRECT are both volatile, the OFFSET formula is better
because it makes fewer function calls. If you really, really want the exact
functionality of

=INDIRECT(ADDRESS(ROW(A1)+5,COLUMN(A1)))

then better to dispense with the ADDRESS call and use INDIRECT's 2nd
argument.

=INDIRECT("R"&(ROW(A1)+1)&"C"&COLUMN(A1),0)

INDIRECT.

HTH
Jason
Atlanta, GA

-----Original Message-----
So, I am trying to use a number stored in a cell to look
that number of cells
up, down, right, or left of a different cell. I have
figured out how to get
the address of the new cell using the ADDRESS function.
For example:
=ADDRESS(ROW(A1)+5, COLUMN(A1)). This will look 5 cells
down from A1.
However, this just returns the address of the cell.
That equation would
return "A6." What funciton can I nest ADDRESS in to
give me the contents of
this new cell?
.