In Excel, I am aware of how to create a named range.

A formula is in the last cell of a spreadsheet that has several columns acting as parameters. Each row has a separate set of data, which is repeated numerous times, and the formula is changed to correspond to the appropriate row index.

However, the formula looks like this (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 are updated automatically to reflect the specific row's column. For each cell, I can make a named range, but that is useless. I am able to name the range in the column, but I am unable to figure out how to include an offset in the calculation.

Additionally, because the entire purpose of this is readability, it probably doesn't help much if the function call turns out to be some horribly convoluted formula.

Oct 24, 2022 in Others 418 views

## 1 answer to this question.

Let's say I have the following figures put up in rows 2 to 4 and columns D to F:

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

Let's say I want column F to be input3, column E to be input2, and column D to be input1.

In Insert > Name > Define...

```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)
```

Now if I write my formula in column G as follows I should get the correct answers:

```G2 =(10*input1+20*input2+30*input3) // 1000
G3 =(10*input1+20*input2+30*input3) // 140
G5 =(10*input1+20*input2+30*input3) // 2000```
• 63,720 points

## How do I transpose a column to a matrix in Excel?

With data in column A, pick some cells ...READ MORE

## How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

## How to build a custom column formula in Power query that will retrieve Image and load it to Excel to display that image from the specified column

Make sure ignore privacy is selected in ...READ MORE

## Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

## Multiplying cells and sum then for a range

Use SUM() as an Array formula:  =SUM(IFERROR((AB22:AB1100 = ...READ MORE

## INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX(\$I\$2:\$I\$26,MATCH(V4,\$R\$2:\$R\$26,0))) The calculation ...READ MORE