I’m trying to make a timeline that can auto-populate a cell contents based on which week they should begin. I would like to provide excel with information like this….
| Task | First Week | Second Week | 3rd Week |
|---|---|---|---|
| Project 1 | 40 | 40 | 20 |
| Project 2 | 20 | 40 | 40 |
Then I want be able to see this as a timeline once I enter the "start week" info.
| Task | Start Week | Week 1 | Week 2 | Week 3 | Week 4 | Week 5 |
|---|---|---|---|---|---|---|
| Project 1 | 1 | 40 | 40 | 20 | ||
| Project 2 | 3 | 20 | 40 | 40 |
I tried working with XLOOKUP, IF, and OFFSET, and got a lot of "SPILL" errors. Thanks for you help in advance!
>Solution :
With an IF and INDEX/MATCH and some math:
=IF(COLUMN(A$1)<$H2,"",IFERROR(INDEX($B$2:$D$3,MATCH($G2,$A$2:$A$3,0),COLUMN(A$1)-$H2+1),""))
That goes in I2 then copy/drag over and down.
