Excel: get content of a cell given the row and column numbers (ADDRESS, INDIRECT, ROW, COLUMN)
Posted by jpluimers on 2015/08/28
A while ago, I needed to do calculations on partially absolute cell references: for some number of rows, the cells needed to be fixed to the top row of each row group.
For a pure absolute cell reference, you’d prepend a dollar sign to the row or column of a cell. So A1 would become $A1 (to make column A absolute), A$1 (to make row 1 absolute) or $A$1 (to make both column A and row 1 absolute).
There is a nice short cut function key F4 to do this.
Excel does not have a built-in partially absolute cell reference solution.
For all these functions, the ROW and COLUMN numbering starts at 1 (one) not 0 (zero).
The way I solved it was to added the below columns (first the reference:heading, then the formula).
The values in the formulas are for ROW 2 (cells A2..XFD2).
- AF: Calculation
- AG: ZLookup
- AH: Row
Calculates the fixed row of the row group. There are 12 rows per group. ROW numbers start at 1, and there is one heading row, hence the 2+ and the -2.
Without TRUNC, the ROW result would be rounded (that is the default floating point to integer conversion that Excel uses).
There is no need to reference a specific row when calling ROW: if you leave it out, it will return the number of the current row.
returns the address of the calculated ROW (from AH) combined with the
Depending on the value of the D column, it calculates the outcome by combining
- Excel: get content of a cell given the row and column numbers – Stack Overflow.
- ADDRESS function – Excel.
- INDIRECT function – Excel.
- COLUMN function – Excel.
- ROW function – Excel.
- TRUNC function – Excel.
- Use an absolute cell reference – Get to know Excel: Enter formulas.
- Excel functions alphabetical list.