In MS Excel 2013, when I write
=CELL("contents",$B$1)It is okay, but when I write
=CELL("contents",ADDRESS(1,2))It results in error. I would like to access the contents of an address. I have done it before, but forgot for the moment.
Correcting this idea or another idea both would be welcome.
1 Answer
When you enter =CELL("contents",ADDRESS(1,2)) you are expecting Excel to evaluate that expression to =CELL("contents",$B$1).
This isn't quite what Excel does. Excel will read it as =CELL("contents","$B$1"). Note the quotes - Excel thinks this is a string, not a cell reference. This is why you get an error.
To convert the string output of ADDRESS() to a cell ref you can adjust your formula slightly like this:
=CELL("contents",INDIRECT(ADDRESS(1,2)))The INDIRECT() function simply converts your string into a real cell reference Excel can use.
