10

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 1

11

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.

2
  • 1
    You still deserve kudos for that. I needed to store an address in a cell to be used as parameter for a VLOOKUP. Jan 24, 2020 at 11:56
  • Glad it helped :)
    – Andi Mohr
    Jan 25, 2020 at 12:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .