How to use a named column in Excel formulas

Question

I know how to make a named range in Excel.

I have a spreadsheet, with various columns going across as parameters, and then finally a formula in the last cell. This is repeated many times in each row, with each row having a different set of data, and the formula updated to reference the correct row index.

However, the formula looks like (three rows worth):

=G2*(10*D2 + 20*E2 + 5*F2)
=G3*(10*D3 + 20*E3 + 5*F3)
=G4*(10*D4 + 20*E4 + 5*F4)

I would like to use named ranges, but I can't find a way to do something like

=Count * (10*var1 + 20*var2 + 5*var3)

where count, var1, var2, and var3 automatically update to be the particular column of the current row. I can create a named range for every cell, but that isn't helpful. I can name range the column, but then I can't find a way to put an offset into the formula.

Also the whole point of this is readability, so if it ends up being some nasty complex formula function call, that probably doesn't help too much.

Answer 1

Simple, at least when using Excel 2010:

1. name your column: select full column, enter name
2. use column name in formula; Excel will combine the referenced column with the current row to access a single cell.

Using the example from Alex P:

1. select column D by clicking the column header containing the "D", enter name "input1" into name field, and press Enter.
2. repeat for columns E to F, using "input2" and "input3", respectively.
3. Do not define additional names defining names "input1" [...] as in example above!
4. use the formula as given in the example above

Attention:

Using named columns this way, you cannot access any other row as the one your formula is in!

At least I'm not aware of the possibility to express something like <ColName>(row+1)...