"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)