question

Luis-5395 avatar image
0 Votes"
Luis-5395 asked emilyhua-msft commented

Help on improving a free template

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
86364-2021-04-09-13-23-14-roteiro-agil-excel.jpg

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




office-excel-itpro
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

emilyhua-msft avatar image
0 Votes"
emilyhua-msft answered emilyhua-msft commented

@Luis-5395

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

86950-5.png

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

86954-6.png

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()
86909-7.png

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.


5.png (16.5 KiB)
6.png (28.3 KiB)
7.png (18.2 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you so much!!

Can you send me that sheet?

I did everything exactly as you instructed, but only the due date worked. I'll keep trying though :)

0 Votes 0 ·

@Luis-5395
Thanks for your reply.
Please note, after entering the formulas for 4 status on Roteiro Ágil, you need to press Ctrl + Shift + Enter at the same time, then you would find the fomulas would be in braces ({}).



1 Vote 1 ·

You're the best, Crtl+shift+enter did the trick.

Thanks again!





0 Votes 0 ·
Show more comments