MrExcel Publishing
Your One Stop for Excel Tips & Solutions

result of ADDRESS function


Posted by Christophe Bouhier on January 21, 2002 7:29 PM

Hi,

I am concateneting text to create a filename & worksheet text which I use in the ADDRESS function to get a reference. But how do I retrive the actual value?

example:
C1 = [filename]sheet1
C2 = ADDRESS(1,1,,,C1)

The result = '[filename]sheet1'!$A$1
But how can I get the real value of this new reference?

TIA / Christophe


Posted by Barrie Davidson on January 21, 2002 7:34 PM

You can use the INDIRECT function but that will only return a value when the file ([filename]) is open.

=INDIRECT('[filename]sheet1'!$A$1)

Hope this helps you out,
BarrieBarrie Davidson

Posted by Aladin Akyurek on January 22, 2002 12:11 AM

Or:

=INDIRECT(ADDRESS(1,1,,,C1))

to be entered in C2.

Aladin

Posted by dzone on January 22, 2002 1:10 AM

Thanks a lot! this works.

My next challenge is to do a SUM of reffered values. Can ADDRESS return a reference range ?
like A1:A10?

Posted by Aladin Akyurek on January 22, 2002 1:25 AM

=ADDRESS(1,1)&ADDRESS(10,1)

will return $A$1:$A$10 which can be fed to SUM.

There are also other ways to sum over a "computed range". But, what is exactly that what you need to do?


Posted by on

My next challenge is to do a SUM of reffered values. Can ADDRESS return a reference range ?