Remember Me?

#### Menu

#1
April 5th 05, 02:59 AM
 sweeney Posts: n/a
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?

#2
April 5th 05, 03:20 AM
 Harlan Grove Posts: n/a

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

#3
April 5th 05, 03:23 AM
 Jason Morin Posts: n/a

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?
.

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post mpierre Excel Worksheet Functions 1 December 29th 04 06:57 AM spydog27 Excel Worksheet Functions 1 December 23rd 04 08:40 AM Sarah Excel Discussion (Misc queries) 3 December 15th 04 06:02 PM StarGazer Excel Worksheet Functions 1 October 28th 04 08:39 AM StarGazer Excel Worksheet Functions 1 October 28th 04 07:09 AM

All times are GMT +1. The time now is 11:00 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright ©2004-2019 ExcelBanter.
The comments are property of their posters.

# About Us

"It's about Microsoft Excel"

Copyright © 2017