Dynamically Referencing Rows

George Manolas

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,

SoS | Dan Palenchar

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).