How to use named column in excel formulas

Question

I know how to make a named range in excel.

I have a spreadsheet with various columns going through the parameters, and then finally the 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 is updated to refer to the correct row index.

however, the formula looks like (3 lines):

=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 cannot find a way to do something like

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

where count, var1, var2 and var3 are automatically updated to be the specific column of the current row. I can create a named range for each cell, but this is not useful. I can name the range of the column, but then I cannot find a way to put the offset in the formula.

, , , - , , , .

Answer 1

, , D F 2-4:

    D    E    F    G
2   10   15   20
3   1    2    3
4   20   30   40

, , D input1, E input2, F - input3:

> > ...

input1 RefersTo =OFFSET(Sheet1!$D$2,0,0,COUNT(Sheet1!$D:$D),1)
input2 RefersTo =OFFSET(Sheet1!$E$2,0,0,COUNT(Sheet1!$E:$E),1)
input3 RefersTo =OFFSET(Sheet1!$F$2,0,0,COUNT(Sheet1!$F:$F),1)

, G , :

G2 =(10*input1+20*input2+30*input3) // 1000
G3 =(10*input1+20*input2+30*input3) // 140
G5 =(10*input1+20*input2+30*input3) // 2000

?

Answer 2

, , Excel 2010:

• : ,
• ; excel .

"Alex P":

• D, , "D", "input1" , enter.
• E-F, "input2" "input3" .
• , "input1" [...], !
• , .

: , , , ! , - ( + 1)...

Answer 3

:

=OFFSET(Sheet1!$D$2,0,0,COUNT(Sheet1!$D:$D),1)

input1,

=Sheet1!$D$2:INDEX(Sheet1!$D:$D,COUNT(Sheet1"$D:$D))

- , .. , . !

Answer 4

VBA, , MyCol, ( ). MyCol ( 12), :

Cells(12, Range("MyCol").Column)

Answer 5

. A1:H4, > > ( Mac). A2:H4 , A1:H4 - .

:

• , , , . Count, Radius, Density, Height
• , =[@Count]*(10*[@Radius] + 20*[@Density] + 5*[@Height])
• H2, Excel "" H. , .
• , ( H4) Tab. Excel , "" .
• , . Excel . , " " "▼ ▲", , .
• , , Excel (, Count G).

" Excel " , .

Answer 6

, row(). , . , :

=offset(NamedColumn1, row()-1)

-1 - , , 1, , 1, 0.

?

Answer 7

Excel named http://office.microsoft.com/en-us/excel-help/define-named-cell-references-or-ranges-HP005201536.aspx

1) 2) "" ( )

, , k0

. , .

= * *

Answer 8

, , , , OP. , , .; -)

Excel , " ". Excel, .

FWIW, , Excel 2007.

:

, E2 =[@Count] * (10*[@Var1] + 20*[@Var2] + 5*[@Var3]), , jason-coyne.

, ( , , ). , , , "".

. (. "" " " ).

, ( ). , Excel . D'!

, , ('), . , , , .

, : https://filebin.ca/3vfaSDn4NLEA/SampleWorkbook.xlsx

Answer 9

, @Dror. :

Range("MyCol").Rows(12)