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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions
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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

Thank you, this workaround solve it!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 220 Industry Talk
 458 Announcements
 4.8K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!