Range with row numbers derived from another cell

Zsolt

Hi all!

If I refer to a range as [D1]6:[D1]8 in a SUM (where my column id is D1 and the range contains rows 6,7,8), is there a way to replace the hard-coded 6 and 8 by values stored in another cell. For example, let's say [code]1 contains 6.

Is there way to use that value of the cell [code]1 inside the formula: [D1][code]1 ? Or use INDEX to retrieve the value of 6 from [code]1 and use that return value in the range?

Thanks!

Genevieve P.

Hi @Zsolt

You could use INDEX to find a value in Column D1 based on a row number specified in Code1, yes:

INDEX([D1]:[D1], Code1)

However, in order to then SUM multiple values based on your Code column, you'd have to specify each cell, like so:

=SUM(INDEX([D1]:[D1], Code1), INDEX([D1]:[D1], Code2), INDEX([D1]:[D1], Code3))

Let me know if this is what you were looking to do!

Cheers,

Genevieve

Genevieve P.

Hi @Zsolt

You could use INDEX to find a value in Column D1 based on a row number specified in Code1, yes:

INDEX([D1]:[D1], Code1)

However, in order to then SUM multiple values based on your Code column, you'd have to specify each cell, like so:

=SUM(INDEX([D1]:[D1], Code1), INDEX([D1]:[D1], Code2), INDEX([D1]:[D1], Code3))

Let me know if this is what you were looking to do!

Cheers,

Genevieve

Zsolt

Thank you, this workaround solve it!