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: ADDRESS, INDIRECT, COLUMN, ROW.
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
- =IF(D2=”*”;INDIRECT(AG2)&X2;””)
- AG: ZLookup
- =ADDRESS($AH2;COLUMN(Z2))
- 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:
The Wiert Corner - irregular stream of stuff 