Hello All,
I am sure this is a very easy formula but I am having trouble. I have attached a workbook that contains a table of 8 rows of tasks with start date, end date and status for project 1, project 2 etc..
I would like to return a list of the in progress or not started tasks for Project 1 but I only want to return the top 3 based on start date.
Please see attached with example.
Thanks in advance for the help
Michele
Hi Michele,
You didn't mention what version of Excel you have, so I have assumed you have 365, in which case you can use this formula:
=CHOOSEROWS(FILTER(Table1[[Project]:[Status]],(Table1[Project]="Project 1")*(Table1[Status]<>"Complete")),{1;2;3})
Mynda
Sorry Mynda, I have excel 2019 and looks like I can't use the filter nor the chooserows functions.
Hi Michele,
In that case, select a range of cells 3 rows high and 5 columns wide, then enter this formula:
=INDEX(Table1[[Project]:[Status]],SMALL(IF((Table1[Status]<>"Complete")*(Table1[Project]="project 1"),ROW(1:8)),{1;2;3}),{1,2,3,4,5})
And press CTRL+SHIFT+ENTER to complete the formula.
Note: if your table has more than 8 rows, then change the 8 in the ROW formula value accordingly.
Mynda
