Is it possible to use the ADDRESS function within the CELL functiion?

[JudeG]

I would like to use the ADDRESS function within the CELL ("contents") function to access a cell's contents based on a constructed address. This will be used to copy data from a sheet containing a master list to other sheets based on a unique number in cell $A$2 on each sheet to receive the information from sheet "T"

The ADDRESS function (e.g. =ADDRESS(2,9+$A$2,4,TRUE,"T") seems to generate the proper cell address within sheet "T" (e.g. T!P2). And this address can be typed directly into the CELL function to access the contents of the designated CELL. (e.g. =CELL("contents",T!P2).

But when the ADDRESS function is used within the CELL function (e.g. =CELL("contents", ADDRESS(1,9+$A$2,4,TRUE,"T")) I get an error stating "There's a problem with this formula".

Is the ADDRESS function not allowed with the CELL function, or is something else wrong with the formula shown?

THANKS

[#2 · Accepted solution]

@JudeG 

CELL's signature is looking for a reference. ADDRESS returns text. You'd need to convert the results of ADDRESS to a reference as such:

=CELL("contents",INDIRECT(ADDRESS(1,9+A1,4,1,"T")))

My thoughts are if you're going to use the above, you might as well simply use INDIRECT.

[#3]

@Patrick2788 

INDIRECT(ADDRESS( . . . ) worked just fine! As you suggest, I'll look into just using INDIRECT. (Simpler is always better).

Many thanks for the quick response.

Jude

[#4]

You're welcome!