Help to select a value based on row number

Liz Smart · 07/15/24

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?

jmyzk_cloudsmart_jp · 07/15/24

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

https://app.smartsheet.com/b/publish?EQBCT=505680daf8344ec2a53d89c194be4298

jmyzk_cloudsmart_jp · 07/15/24

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

https://app.smartsheet.com/b/publish?EQBCT=505680daf8344ec2a53d89c194be4298

KPH · 07/15/24

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 auto-number as your identifier.

The syntax would be

=INDEX([Actual % Complete]@row, MATCH([pred_1]@row, [auto-number col]:[auto-number col], 0))

Where auto-number 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 auto-number 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.

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Hope this helps. Let us know if you have any questions.