Link two cells

Vinicius Miguel · 05/26/23

Hello everyone,

I'm new working with smartsheet and seeing that he does not have all excels formulas. I need to change a "status" when I change a number in a column.

For example, when I change the column "Status da entrega" to "Aprovada", the next line change "Status da atividade" to "Liberado" and it is working fine with this formula:

IF([Status da entrega]5 = "APROVADA"; "LIBERADO"; IF([Status da entrega]5 = "EM REVISÃO"; "AGUARDANDO"; IF([Status da entrega]5 = "REPROVADA"; "BLOQUEADO")))

My problem is, I would like to add a new criteria to read the column "Antecessor". Every line that have the same number in column "Antecessor" should change the "Status da atividade" too.

Does anyone know a way to do it?

Thanks!

Vinicius Miguel

Genevieve P. · 05/30/23

Hi@Vinicius Miguel

There is a way to do this but it's a little complicated and requires a few extra columns. You also won't be able to make this a column formula as we're referencing the cell above, as it seems like you're doing in your original formula - drag-fill the formula down instead.

The 2 column you need to add are as follows:

ASystemAuto-Number column (I called mine "Auto")
aText/Numbercolumn with a column formula (I called mine "Row")

The Row column has a formula that will always bring back the current row number, based on the Auto Number column, that will update if rows are rearranged:

=MATCH([email protected]; Auto:Auto; 0)

Now your formula can use this to see theMINrow to bring back, based on the Row column and the matching Antecessor.

We start with an IF statement that says, if this current row hasno other rows before itwith the same "Antecessor", OR if the current row has a blank "Antecessor" then use your original formula:

=IF(OR(COUNTIF(Antecessor$1:[email protected]; @cell =[email protected]) = 1;[email protected]= ""); IF([Status da entrega]5 = "APROVADA"; "LIBERADO"; IF([Status da entrega]5 = "EM REVISÃO"; "AGUARDANDO"; IF([Status da entrega]5 = "REPROVADA"; "BLOQUEADO")));

But then if that's not true, meaning there's a row above this current one that has the same Antecessor, then we can use an INDEX(-MIN(COLLECT()) combination toindexthe "Status da entrega" column and return the value from the rowabove(-1) the matching Minimum value row.

In plain language:

Find the earliest row in the sheet that has the same Antecessor as this current row
Then subtract 1 from that row number
And bring back the word from the row above it in the Status da entrega column
If that word matches "this" word, return "this other" word

IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell =[email protected])) - 1) = "APROVADA"; "LIBERADO"; IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell =[email protected])) - 1) = "EM REVISÃO"; "AGUARDANDO"; IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell =[email protected])) - 1) = "REPROVADA"; "BLOQUEADO"))))

Full Formula:

=IF(OR(COUNTIF(Antecessor$1:[email protected]; @cell =[email protected]) = 1;[email protected]= ""); IF([Status da entrega]5 = "APROVADA"; "LIBERADO"; IF([Status da entrega]5 = "EM REVISÃO"; "AGUARDANDO"; IF([Status da entrega]5 = "REPROVADA"; "BLOQUEADO"))); IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell =[email protected])) - 1) = "APROVADA"; "LIBERADO"; IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell =[email protected])) - 1) = "EM REVISÃO"; "AGUARDANDO"; IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell =[email protected])) - 1) = "REPROVADA"; "BLOQUEADO"))))

Let me know if that works for you!

Cheers,

Genevieve

Vinicius Miguel · 05/29/23

Hello folks,

Does anyone know a solution for this question?

Best regards,

Vinicius Miguel

Genevieve P. · 05/30/23

Hi@Vinicius Miguel

这个公式怎么知道这两个r的哪一个ows has the "correct" new status? Will it always be the row that's higher up on the sheet (the first row) or could it sometimes be that the lower row has the new status?

Vinicius Miguel · 05/30/23

Hi@Genevieve P.,

Thanks for your follow up.

In this case, it will always be the higher row, the first one.

Best regards,

Vinicius Miguel

Genevieve P. · 05/30/23

Hi@Vinicius Miguel

There is a way to do this but it's a little complicated and requires a few extra columns. You also won't be able to make this a column formula as we're referencing the cell above, as it seems like you're doing in your original formula - drag-fill the formula down instead.

The 2 column you need to add are as follows:

ASystemAuto-Number column (I called mine "Auto")
aText/Numbercolumn with a column formula (I called mine "Row")

The Row column has a formula that will always bring back the current row number, based on the Auto Number column, that will update if rows are rearranged:

=MATCH([email protected]; Auto:Auto; 0)

Now your formula can use this to see theMINrow to bring back, based on the Row column and the matching Antecessor.

We start with an IF statement that says, if this current row hasno other rows before itwith the same "Antecessor", OR if the current row has a blank "Antecessor" then use your original formula:

=IF(OR(COUNTIF(Antecessor$1:[email protected]; @cell =[email protected]) = 1;[email protected]= ""); IF([Status da entrega]5 = "APROVADA"; "LIBERADO"; IF([Status da entrega]5 = "EM REVISÃO"; "AGUARDANDO"; IF([Status da entrega]5 = "REPROVADA"; "BLOQUEADO")));

But then if that's not true, meaning there's a row above this current one that has the same Antecessor, then we can use an INDEX(-MIN(COLLECT()) combination toindexthe "Status da entrega" column and return the value from the rowabove(-1) the matching Minimum value row.

In plain language:

Find the earliest row in the sheet that has the same Antecessor as this current row
Then subtract 1 from that row number
And bring back the word from the row above it in the Status da entrega column
If that word matches "this" word, return "this other" word

IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell =[email protected])) - 1) = "APROVADA"; "LIBERADO"; IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell =[email protected])) - 1) = "EM REVISÃO"; "AGUARDANDO"; IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell =[email protected])) - 1) = "REPROVADA"; "BLOQUEADO"))))

Full Formula:

=IF(OR(COUNTIF(Antecessor$1:[email protected]; @cell =[email protected]) = 1;[email protected]= ""); IF([Status da entrega]5 = "APROVADA"; "LIBERADO"; IF([Status da entrega]5 = "EM REVISÃO"; "AGUARDANDO"; IF([Status da entrega]5 = "REPROVADA"; "BLOQUEADO"))); IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell =[email protected])) - 1) = "APROVADA"; "LIBERADO"; IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell =[email protected])) - 1) = "EM REVISÃO"; "AGUARDANDO"; IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell =[email protected])) - 1) = "REPROVADA"; "BLOQUEADO"))))

Let me know if that works for you!

Cheers,

Genevieve

Link two cells

Best Answer

Answers

Help Article Resources

Categories