Help on improving a free template

Question

Hello there!

I found a very good templante of a Kanban model by the name of "Roteiro Ágil" (Agile Script).
You change the status on the table and the card changes location automatically.

I want to improve on that.
In the table you can have a status. task, priority and results. I want to add a owner and the due data. to the card

I tried adding new colums, conditional formating (so when due data turns red), but nothing works :(

So, I came to the excel masters.
Can you guys help me out?

Thanks a lot!!!

86401-2021-04-09-13-23-39-roteiro-agil-excel.png

I tried to add the worksheet here, but it didn't worked
So I uploaded to onedrive:

https://1drv.ms/x/s!Anom3OPskl3ikkwkCbnBV8hwtb8Z?e=STDxNW

Answer 1

@Luis

I add "Owner" and "Due Date" on Dados do Roteiro as following image. Please note, I set **Text format for "Due Date".**

Then I add a little formula to the original formula for 4 status on Roteiro Ágil.

• Cell B3 under Completed, =IFERROR(INDEX(Atividade,SMALL(IF(Meta=$B$2,ROW(Atividade)-MIN(ROW(Atividade))+1),ROWS($B$3:B3)))&" "&CHAR(10)&(INDEX(Resultado,SMALL(IF(Meta=$B$2,ROW(Resultado)-MIN(ROW(Resultado))+1),ROWS($B$3:B3))))&CHAR(10),"")&IFERROR(INDEX(DadosDoRoteiro,SMALL(IF(Meta=$B$2,ROW(Atividade)-MIN(ROW(Atividade))+1),ROWS($B$3:B3)),5)&CHAR(10),"")&IFERROR(INDEX(DadosDoRoteiro,SMALL(IF(Meta=$B$2,ROW(Atividade)-MIN(ROW(Atividade))+1),ROWS($B$3:B3)),6),"")
• Cell D3 under Atual, =IFERROR(INDEX(Atividade,SMALL(IF(Meta=$D$2,ROW(Atividade)-MIN(ROW(Atividade))+1),ROWS($B$3:B3)))&CHAR(10),"")&IFERROR(INDEX(DadosDoRoteiro,SMALL(IF(Meta=$D$2,ROW(Atividade)-MIN(ROW(Atividade))+1),ROWS($B$3:B3)),5)&CHAR(10),"")&IFERROR(INDEX(DadosDoRoteiro,SMALL(IF(Meta=$D$2,ROW(Atividade)-MIN(ROW(Atividade))+1),ROWS($B$3:B3)),6),"")
• Cell F3 Short Term, =IFERROR(INDEX(Atividade,SMALL(IF(Meta=$F$2,ROW(Atividade)-MIN(ROW(Atividade))+1),ROWS($B$3:B3)))&CHAR(10),"")&IFERROR(INDEX(DadosDoRoteiro,SMALL(IF(Meta=$F$2,ROW(Atividade)-MIN(ROW(Atividade))+1),ROWS($B$3:B3)),5)&CHAR(10),"")&IFERROR(INDEX(DadosDoRoteiro,SMALL(IF(Meta=$F$2,ROW(Atividade)-MIN(ROW(Atividade))+1),ROWS($B$3:B3)),6),"")
• Cell H3 under Future, =IFERROR(INDEX(Atividade,SMALL(IF(Meta=$H$2,ROW(Atividade)-MIN(ROW(Atividade))+1),ROWS($B$3:B3)))&CHAR(10),"")&IFERROR(INDEX(DadosDoRoteiro,SMALL(IF(Meta=$H$2,ROW(Atividade)-MIN(ROW(Atividade))+1),ROWS($B$3:B3)),5)&CHAR(10),"")&IFERROR(INDEX(DadosDoRoteiro,SMALL(IF(Meta=$H$2,ROW(Atividade)-MIN(ROW(Atividade))+1),ROWS($B$3:B3)),6),"")

On the following image, I also highlight the Expiry date which is equal to or less than today's date.
I select some cells of "Atual", then enter the following formula in Conditional Formatting.
DATE(LEFT(RIGHT($D3,10),4),MID(RIGHT($D3,10),6,2),MID(RIGHT($D3,10),9,2))<=TODAY()

Hope the information could be helpful.

---

f an Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.