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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With