# Concatenating a column letter and a cell value and using it as a reference to a formula

I have a SUM formula `=SUM(F1:F10)`, how can I make the "10" dynamic by referencing to a cell value? For example, cell `D2` has the value 10 so my goal is to concatenate "F" with the value of cell `D2`. If I change the cell value of `D2` from 10 to 20 then the formula should update.

I am thinking of something like: `=SUM(F7:INDIRECT("F" & TEXT(\$D\$2,"0")))`

But I need the `INDIRECT("F")` dynamic as it formula needs to be applied to column G as well.

Sorry if this question is elementary but I already forgot how it works.

An alternative is to use the `=OFFSET()` function to define your summand.

For example `OFFSET(F1,0,add_col,rows_to_sum,1)` defines a range 1 column wide which starts at `add_col` columns to the right of cell `F1` extends `row_to_sum` rows downwards, so

`OFFSET(F1,0,0,10,1)` is equivalent to `F1:F10`
`OFFSET(F1,0,0,20,1)` is equivalent to `F1:F20`
`OFFSET(F1,0,1,10,1)` is equivalent to `G1:G10`
`OFFSET(F1,0,1,20,1)` is equivalent to `G1:G20`

`add_col` and `rows_to_sum` can be suitable cell references e.g. `A1`, `D2` etc.

Simply wrap the `OFFSET()`within a `=SUM()` to get something like `=SUM(OFFSET(F1,0,A1,D2,1))` using relative/absolute cell references (\$ signs) as appropriate.

If you don't always wish to start summation from the first worksheet row then use a value other than 0 for the second argument of the `OFFSET()` function. For example:

`OFFSET(F1,6,0,14,1)` is equivalent to `F7:F20`

and again a cell reference can be used for this second argument.

Similarly, if you want to make the summation cover multiple columns then use a value other than 1 for the fifth argument of the `OFFSET()` function. For example:

`OFFSET(F1,6,0,14,2)` is equivalent to `F7:G20`

and again a cell reference can be used for this fifth argument.

See https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66 for details about worksheet function `=OFFSET()`

`SUBSTITUTE(ADDRESS(1,F7,4),"1","")` will return the column letter for cell `F7`. This will help get the column, you might be able to incorporate this with your formula by using this:

`=SUM(F1:INDIRECT(SUBSTITUTE(ADDRESS(1,F7,4),"1","") & TEXT(\$D\$2,"0")))`

• Do you really mean `F7` in `ADDRESS(1,F7,4)`? For this expression not to return a `#VALUE!` error cell `F7`will need to contain a valid worksheet column number - so 6 for worksheet column `F`, 7 for `G`, etc. But `F7` is the start cell of the summand range, so it will contain whatever needs to be summed. An alternative would be to use a different cell, say `D1` in the `ADDRESS()` snippet but, in this case, why not more simply dispense with `SUBSTITUTE(ADDRESS))` and put the column letter in `D1` and use `=SUM(F7:INDIRECT(D1&TEXT(\$D\$2,"0")))` instead?
– DMM
Commented Feb 2, 2022 at 12:08
• Thanks @DMM , the first argument was incorrect, I've updated the answer. Your `OFFSET` solution is quite elegant - thanks for teaching me something new! Commented Feb 2, 2022 at 15:42