Help to select a value based on row number
Hi all  I am trying to determine if a task is ready to start based on all of the predecessors being 100% complete. I have numerous tasks with more than 1 predecessor and I've parsed out all the predecessors into individual columns (pred_1, pred_2, etc) like this
I'd like for the ready to start column to indicate yes, if the average % completion of all the predecessors is 100%. I'm struggling with how to bring in the Actual % Complete using the row number in the P1%, P2%, etc columns. If I type a formula into the Yellow cell above = [Actual % Complete]1, 100% appears. But I'd like to make this formula based and assign the row number based on the value in the Pred_1 column. I tried adding this formula to the Yellow cell : =[Actual % Complete]pred_1@row, which results in 'unparsable'
Can anyone help me on the syntax of the formula to put in the yellow cell that would place the value of 'Actual % Complete' from the row number that is in the pred_1 column?
Best Answer

Hi @Liz Smart
You can use the INDEX function like this;
[P1 %] =IF(ISNUMBER([pred_1]@row), INDEX([Actual % Complete]:[Actual % Complete], [pred_1]@row))
[P2 %] =IF(ISNUMBER([pred_2]@row), INDEX([Actual % Complete]:[Actual % Complete], [pred_2]@row))
Answers

Hi @Liz Smart
You can use the INDEX function like this;
[P1 %] =IF(ISNUMBER([pred_1]@row), INDEX([Actual % Complete]:[Actual % Complete], [pred_1]@row))
[P2 %] =IF(ISNUMBER([pred_2]@row), INDEX([Actual % Complete]:[Actual % Complete], [pred_2]@row))

Hi @Liz Smart
Unfortunately, you cannot use a cell value to specify a row number, you can only enter the row number or use @row to refer to the current row.
You could however, use something like INDEX MATCH to find the correct row, if there is some identifier. You could add an auto number column to your sheet and then use the autonumber as your identifier.
The syntax would be
=INDEX([Actual % Complete]@row, MATCH([pred_1]@row, [autonumber col]:[autonumber col], 0))
Where autonumber col is the new column that numbers every row and the same number is typed into pred_1
If you don't want to use an autonumber column you could manually add a number to each row.
Or it could be anything else you want to use that is unique to the row. So long as the value in pred_1 matches the value in the new column, the value in the Actual % Complete column will be returned.
Hope this helps. Let us know if you have any questions.

This was EXACTLY what I was looking for! Thank you so VERY much 😍

Happy to help! @Liz Smart😁
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 123 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!