Dynamically Referencing Rows

Hi Guys,

I have a sheet with multiple Parents and their Children. Each Parent may have any number of Children.

For each Parent record I need to be able to set a new column value in the Parent record by referencing a particular Child record by its row number. I can easily determine the row number of the particular Child record I want, but how can I use this Childs row number in a cell reference?

I.e. I know i want to reference the [Task Price] in the second child row, which has RowID 27. This value 27 is stored in a cell called Index. I would like to simply go =[TaskPrice]Index but this doesn't work.

Is there a way to dynamically set the row pointer to reference a cell in another row.

@row only references the current row and you cannot set the value of @row.

The whole aim of this is to merge data from selected Children into the Parent record so I can generate a document with info from multiple records.

Thanks,

Best Answer

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Answer ✓

    Hello @George Manolas!

    You can use INDEX(MATCH()) for this if I am understanding it all correctly. I believe you are trying to get the TaskPrice of an item based on the Index number in the same row which points to a different RowID. Try using:

    =INDEX([TaskPrice]:[TaskPrice], MATCH([Index]@row, [RowID]:[RowID],0))

    For more info see the INDEX() function page.

    You might need to change the 0 at the end of the function if your Sheet is sorted based on RowID (1 for ascending, -1 for descending if memory serves me correctly).

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!