# The Wiert Corner – irregular stream of stuff

• ## Email Subscription

Join 1,706 other followers

## 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.

To solve this, I used these addressing functions: ADDRESSINDIRECTCOLUMNROW.

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).

1. AF: Calculation
• =IF(D2=”*”;INDIRECT(AG2)&X2;””)
2. AG: ZLookup
3. AH: Row
• =2+12*TRUNC((ROW()-2)/12)

### Column AH

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.

### Column AG

returns the address of the calculated ROW (from AH) combined with the

### Column AF

Depending on the value of the D column, it calculates the outcome by combining

–jeroen

via:

### 2 Responses to “Excel: get content of a cell given the row and column numbers (ADDRESS, INDIRECT, ROW, COLUMN)”

1. ### Michael Estlsaid

It seems like Index is the function you where looking for: =INDEX(\$Z\$2:\$Z\$28;12*TRUNC((ROW()-2)/12)+1)&X2

• 