Link two cells

Vinicius Miguel

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.

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:

• A System Auto-Number column (I called mine "Auto")
• a Text/Number column 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(Auto@row; Auto:Auto; 0)

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

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

=IF(OR(COUNTIF(Antecessor$1:Antecessor@row; @cell = Antecessor@row) = 1; Antecessor@row = ""); 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 to index the "Status da entrega" column and return the value from the row above (-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 = Antecessor@row)) - 1) = "APROVADA"; "LIBERADO"; IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell = Antecessor@row)) - 1) = "EM REVISÃO"; "AGUARDANDO"; IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell = Antecessor@row)) - 1) = "REPROVADA"; "BLOQUEADO"))))

Full Formula:

=IF(OR(COUNTIF(Antecessor$1:Antecessor@row; @cell = Antecessor@row) = 1; Antecessor@row = ""); 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 = Antecessor@row)) - 1) = "APROVADA"; "LIBERADO"; IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell = Antecessor@row)) - 1) = "EM REVISÃO"; "AGUARDANDO"; IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell = Antecessor@row)) - 1) = "REPROVADA"; "BLOQUEADO"))))

Let me know if that works for you!

Cheers,

Genevieve

Vinicius Miguel

Hello folks,

Does anyone know a solution for this question?

Best regards,

Vinicius Miguel

Genevieve P.

Hi @Vinicius Miguel

How will the formula know which one of those two rows 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

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.

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:

• A System Auto-Number column (I called mine "Auto")
• a Text/Number column 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(Auto@row; Auto:Auto; 0)

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

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

=IF(OR(COUNTIF(Antecessor$1:Antecessor@row; @cell = Antecessor@row) = 1; Antecessor@row = ""); 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 to index the "Status da entrega" column and return the value from the row above (-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 = Antecessor@row)) - 1) = "APROVADA"; "LIBERADO"; IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell = Antecessor@row)) - 1) = "EM REVISÃO"; "AGUARDANDO"; IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell = Antecessor@row)) - 1) = "REPROVADA"; "BLOQUEADO"))))

Full Formula:

=IF(OR(COUNTIF(Antecessor$1:Antecessor@row; @cell = Antecessor@row) = 1; Antecessor@row = ""); 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 = Antecessor@row)) - 1) = "APROVADA"; "LIBERADO"; IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell = Antecessor@row)) - 1) = "EM REVISÃO"; "AGUARDANDO"; IF(INDEX([Status da entrega]:[Status da entrega]; MIN(COLLECT(Row:Row; Antecessor:Antecessor; @cell = Antecessor@row)) - 1) = "REPROVADA"; "BLOQUEADO"))))

Let me know if that works for you!

Cheers,

Genevieve