CODEWITHSUNDEEP
excel
Jaron
Jaron

Reputation: 117

Excel: Using Cell and Address functions to get contents of dynamically changing addresses

I'm trying to find use excel functions to get the contents of cell addresses that will change; however, whenever I nest the CELL and ADDRESS functions they fail.

Here is the line of code in excel that I am using:

=CELL("contents",ADDRESS(1+index,1,4))

where "index" is a reference to another cell whose contents are dynamic integers that change the row that address should be looking at.

Both of these functions work if they are called individually (i.e. ADDRESS(1+index,1,4) works and CELL("contents",A1) works). I even tried double calling the cell function on an external cell that returns the dynamic address of the cell I want but the CELL functions seems to error out anytime there is a function in it's reference parameter. Is there another function that can deal with changing addresses?

Upvotes: 0

Views: 1221

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34210

Just to add to @Scott's answer, to make your formula work you would have had to use INDIRECT

=CELL("contents",INDIRECT(ADDRESS(index+1,1,4)))

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

Just use INDEX():

=INDEX(A:A,1+index)

Upvotes: 1

Related Questions