Log In
Register
Home
May 10, 2022
Offline
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
July 16, 2010
Offline
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
May 10, 2022
Offline
Sorry Mynda, I have excel 2019 and looks like I can't use the filter nor the chooserows functions.
July 16, 2010
Offline
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
1 Guest(s)
