Link two cells
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
Best Answer
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hello folks,
Does anyone know a solution for this question?
Best regards,
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?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!