Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get content of a cell given the row and column numbers

I want to get the content of a cell given its row and column number. The row and column number are stored in cells (here B1,B2). I know the following solutions work, but they feel a bit hacky.

Sol 1

=CELL("contents",INDIRECT(ADDRESS(B1,B2))) 

Sol 2

=CELL("contents",OFFSET($A$1, B1-1,B2-1)) 

Is there no less verbose method? (like =CellValue(row,col) or whatever)?

Edit / Clarification: I just want to use the excel worksheet formulas. No VBA. In short, I pretty much look for the equivalent of the VBA Cells() method as an excel Formula.

like image 794
Philipp Avatar asked Jan 27 '11 07:01

Philipp


People also ask

How do you reference a cell by row and column number?

The Excel ADDRESS function returns the address for a cell based on a given row and column number. For example, =ADDRESS(1,1) returns $A$1. ADDRESS can return an address in relative, mixed, or absolute format, and can be used to construct a cell reference inside a formula.

How do I get data from rows and columns in Excel?

Please enter this formula: =INDIRECT(ADDRESS(F1,F2)), and press Enter key to get the result, see screenshot: Note: In the above formula, F1 and F2 indicate the row number and column number, you can change them to your need.


1 Answers

You don't need the CELL() part of your formulas:

=INDIRECT(ADDRESS(B1,B2)) 

or

=OFFSET($A$1, B1-1,B2-1) 

will both work. Note that both INDIRECT and OFFSET are volatile functions. Volatile functions can slow down calculation because they are calculated at every single recalculation.

like image 152
Charles Williams Avatar answered Oct 22 '22 13:10

Charles Williams