Excel 2003 formulas - Is there a more-readable way to use names in place of literal cell references?

[chrisjj]

I often upgrade the readability of formulas by naming a column (technically as a range) and replacing e.g. =D2 by =Status relying on Excel interpreting the range name as a reference to the current row in that range. (Sad though that formula editing's source highlighting doesn't do the same, instead showing highlighting range rather than cell.)



However, replacing =myfunc(D2) by =myfunc(Status) can (depending on myfunc definition) fail with a VALUE error. It seems the interpretation is AWOL.

My workaround is: =myfunc(INDEX(Status,ROW())) . This defeats much of the readability upgrade.

Do you know a workaround that's more readable?

The best I've come up with is a user function - here(<name>) - which is a lot more elaborate in definition and a lot slower in execution.

[ArviLaanemets]

Instead regular table, use Defined Table (Insert>Table)

Then, in case the formula will be in same table, you can refer to cell in same row, like
=[@Status] (where Status is the example name for header of referred column)
In case the formula will not be in same table, you can refer to cell of any Defined Table in same row as the current row for your formula, like
=tTest[@Status]
where tTest is the example of name of Defined Table

You also can refer to whole column of Defined Table, like
=COUNTA(tTest[Status])
Or to column header of Defined Table, like
=tTest[[#Headers];[Status]]
etc.

[chrisjj]

Originally Posted by ArviLaanemets

Instead regular table, use Defined Table (Insert>Table)

Excel 2003, so I tried Data > List.

Originally Posted by ArviLaanemets

Then, in case the formula will be in same table, you can refer to cell in same row, like
=[@Status] (where Status is the example name for header of referred column)

I get this fail:

[ArviLaanemets]

Sorry! Defined Tables are available starting from Excel 2007!

A possible workaround for older versions:
Add to table a column where the row number of table's datarange is calculated, like
=ROW() - HeaderRowNumber
(In case the start row if datarange is always fixed, you can skip this part, and simply calculate the datarange row number whenever it is needed);
Define dataranges of table columns you need to refer to in your formulas as dynamic names. Like the datarange where row numbers mentioned above are calculated. P.e. the row numbers are calculated in column F, and the header row of table is 1, then the formula for named range nRow will be something like

=OFFSET($F$1,1,,MAX($F:$F),1)

;
To get a value from cell in current row of datarange defined e.g. as nTest in same table, use formula (e.g. in row 2 of your worksheet) like

=INDEX(nTest,$F2)

[chrisjj]

I'm not sure $F2 is more readable than ROW(), but thanks very much since I think you've demonstrated no improvement is possible beyond the here() function.