Task list - re-design display grouped by month

Hi,

I worked out a simple task list where I enter line by line things that need to be done starting and ending dates. Now I want to have the tasks displayed on another sheet, but grouped together by month based on the deadline, each month on column. So basically, I want the have one column for each month, and below the tasks that are coming up.

I tried to play around with vlookup but cant return all the different tasks since I do not have a unique ID to match them... Any ideas maybe?

Thanks,
A2k

DADHA Task Management3.xlsx

Hi,

Can u provide the expected result manually for one month so that i can try at least .

Punnam

Hi,

thanks for the reply.
Enclosed an updated sample with the expected outcome on the "Timeline" sheet.

Thanks,
A2k

DADHA Task Management3.xlsx

HI,

Try this file,

I have updated a array formula in "Timelne(2)" sheet, but for instance i have considered Month as Numerical in row 15. if you like them in name, i can even fix that. Let me know interest .

Punnam

It would be great if I could have the updated attachment please

Hi,

Sorry about the attachment .
Plz find the attachment .

Punnam

Great stuff, thanks a lot Punnam.

I am really not familiar with the Index function, would you mind elaborating on what you did? Would love to have this as a learning example:

Please Login or Register  to view this content.

If I understand the thinking process, it basically is like this:
1. Index the total number of tasks on the index sheet (looping through c17:C246)
2. find the respective row number of the task we are looking for by matching the month of the task (Index B17:B246) with the monthly column on the Timeline sheet (Timeline row B16)
3. from the matching tasks of the respective month, you deduct (?) tasks that fit the month in B17? No idea what this is good for, also what does the SMALL() function do?
4. pack all this into an array and let the SMALL() function return only one row results (?)

Would love if you can enlighten me a little here

Thanks,
A2k

Hi,

I am not good at explanation but i will do the best i can
=IFERROR(INDEX('Action Planning'!$C$17:$C$246,SMALL(IF('Action Planning'!$B$17:$B$246=Timeline!C$15,ROW('Action Planning'!$B$17:$B$246)-ROW(Timeline!

=(IF('Action Planning'!$B$17:$B$246=Timeline!C$15,ROW('Action Planning'!$B$17:$B$246)
By this part of formula , i am able to check the respective months & get the row numbers of specific month of the data om Action Planning
As this is a array function this will {17,18,19,20,21,22,23} all the row number - row() will help you to make {1,2,3,4,5,6,7...},

small
Will give the smallest number in the array Small({1,2,3,4,5,6},2), 2 will give u 2 end smallest , if the same is replaced with 4 will give you 4rth smallest number that array , (tow achieve row number i=we use Row(1:1) will give 1, row(2:2) will u 2).

I think i have explained you up the best i can know make use of "Evaluate formula" in formula auditing tab to know how the If small , Row function is working.

Punnam

@ Armitage2

Is this clear, in case of any clarification i will try to help you

Punnam

Thanks Punnam, this is great. I am trying to learn whenever I get the chance, thanks a lot

Sorry guys, one more problem I just stumbled across.

The monthly breakdown of tasks now works well, but I now also need a Critical Path format based on a weekly performance...

Basically, what I need is a way to lookup the start and end date of the concerned task, then find the starting week and ending week on the Timeline (Weekly) sheet, and as a result fill/color the cells ranging from the start to end date. Alternatively, I would already be happy with just marking all cells between start and end with an X and I will let conditional formatting do the rest.

Any ideas on how to make this happen?
Thanks,
A2k

Task Management Sample.xlsx

A2k,

Can u punch manually the dates for a few tasks in u r "Timeline (Week) (RESULT)"sheet so that i can understand the logic .

Type the starting and ending date for the task which u have colored in u r "Timeline (Week) (RESULT)".

Punnam

Hi Punnam,

I have left some guidance on the sample sheet.
* Start date of Task 1 is 01/01/2014, end date 01/02/2014 as indicated on the ACTION PLANNING sheet
* same for task 2+3+4+5+6
* formula should recognize that the start date is in Week 1 of 2014 and end date is Week 6, then color everything in-between this range
* the start and end date can be looked up via VLOOKUP of B1 from the ACTION PLANNING sheet since each task name is unique
* if the formula cannot automatically color a cell, then just return a "X" and conditional formatting can highlight all cells containing an "X"

Thanks!
A2k

Task Management Sample (rev1).xlsx

One more clarification

Task Number 7 Start Dt : 03-02-2014 & End DT 31-03-2014
And should be colored for K25 to R25

Am i correct

Punnam

Task 7 starts on 01/03/2014 (dd/mm/yyyy) and ends on 01/04/2014
Since the summary displays in weeks, the start would be week #9 (Monday, resp. the first day of this week is the 24/02/2014) and end would be week #14 (Monday = 31/03/2014). that would be the range from M25:R25.

I am not looking at the individual DAY when the task starts/ends, but rather the respective week and need it colored from there onwards.

Thanks a lot for looking so much into this,
A2k

A2k,

Find the updated revised File as requested .

Punnam

Dear Punnam,

thank you very much for all your support. The formulas are working well and I am able to finally close my project. Sorry for the late reply and many thanks again,
A2k

U r welcome
Punnam