Combining address function and cell function in Excel
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.