Range with row numbers derived from another cell

Options
Zsolt
Zsolt ✭✭✭
edited 11/14/21 in Formulas and Functions

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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

  • Zsolt
    Zsolt ✭✭✭
    Options

    Thank you, this workaround solve it!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!