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 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!
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!