Range with row numbers derived from another cell
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 hardcoded 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!
Best Answer

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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Thank you, this workaround solve it!
Help Article Resources
Categories
Check out the Formula Handbook template!