# The Wiert Corner – irregular stream of stuff

• ## Email Subscription

Join 2,513 other followers

# Archive for August 28th, 2015

## Changing a TextBox annotation in an MDI file

Posted by jpluimers on 2015/08/28

Even though MDI files have been abandoned for a while (you can only install it for Office XP, 2003 and 2007), sometimes I need to do some editing of TextBox annotations.

This is not intuitive: you have to right-click the textbox in order to edit it.

Printing in Office 2003 also was non-intuitive: Ctrl-P would crash the Microsoft Office Document Imaging MDI editor with a Microsoft Crash Report. But mouse-clicking the print-icon works. I remember having sending dozens of these reports to Microsoft around 10 years ago, but it never got fixed.

–jeroen

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

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: