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.
This question is related to
excel
excel-formula
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.
Try =index(ARRAY, ROW, COLUMN)
where: Array: select the whole sheet Row, Column: Your row and column references
That should be easier to understand to those looking at the formula.
It took me a while, but here's how I made it dynamic. It doesn't depend on a sorted table.
First I started with a column of state names (Column A) and a column of aircraft in each state (Column B). (Row 1 is a header row).
Finding the cell that contains the number of aircraft was:
=MATCH(MAX($B$2:$B$54),$B$2:$B$54,0)+MIN(ROW($B$2:$B$54))-1
I put that into a cell and then gave that cell a name, "StateRow" Then using the tips from above, I wound up with this:
=INDIRECT(ADDRESS(StateRow,1))
This returns the name of the state from the dynamic value in row "StateRow", column 1
Now, as the values in the count column change over time as more data is entered, I always know which state has the most aircraft.
Source: Stackoverflow.com