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
-
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
-
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!
-
Hi
,Great, that works perfectly.
many thanks,
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!