Forum Discussion
Solved
return rows from list that matches criteria without blanks
Hello,
can you help me with this one, please? I try to create an monthly objective list and track the progress but without success.
I attach the excel file here and I'll explain bellow what is the ideea.
So, I have a MAIN OBJECTIVE LIST table where I add all objectives for the current month and assign a date to each objective. The STATUS column will retrieve data from the tables that are bellow.
example:
Let's say that I assign 3 objectives for the first day (01/08/2023) and two for the next day.
A maximum of 20 objectives can be assigned a month.
| MAIN OBJECTIVE LIST | DATE | STATUS | |
| 1 | OBJECTIVE 1 | 01/08/2023 | |
| 2 | OBJECTIVE 2 | 01/08/2023 | |
| 3 | OBJECTIVE 3 | 01/08/2023 | |
| 4 | OBJECTIVE 4 | 02/08/2023 | |
| 5 | OBJECTIVE 5 | 02/08/2023 |
| 6 | |||
| 7 | |||
| 8 | |||
| 9 | |||
| 10 |
Bellow this table, each day will have it's own table that will retrieve the objectives from the MAIN TABLE only for the dates that match the current date (without blank rows). A maximum of 5 objectives can be assigned daily.
Once an objective is completed, I will assign a status in Today's table. The STATUS column must then be retrieved by the MAIN TABLE.
| 01/08/2023 | TODAY'S OBJECT LIST | DATE | STATUS |
| 1 | OBJECTIVE 1 | 1/8/2023 | SOLVED |
| 2 | OBJECTIVE 2 | 1/8/2023 | SOLVED |
| 3 | OBJECTIVE 3 | 1/8/2023 | PENDING |
| 4 | |||
| 5 |
the other days of the month will work the same.
| 02/08/2023 | TODAY'S OBJECT LIST | DATE | STATUS |
| 1 | OBJECTIVE 4 | 2/8/2023 | PENDING |
| 2 | OBJECTIVE 5 | 2/8/2023 | SOLVED |
| 3 | |||
| 4 | |||
| 5 |
I appreciate your time!
many thanks in advance!
=IFERROR(INDEX($B$3:$B$22,SMALL(IF(($C$3:$C$22=C26)*($C$3:$C$22<>""),ROW($B$3:$B$22)-2),ROW(A1))),"")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
=IFERROR(INDEX($D$26:$D$44,MATCH(1,(B3=$B$26:$B$44)*(C3=$C$26:$C$44),0)),"")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
- This one works nice! thanks OliverScheurich
there is one one thing missing.
How can I apply the same formula in cell B26, to retrieve the objective from B3 that was assigned for that date? 01/august/2023
here is the example:
this table needs to retrieve the objectives from the MAIN OBJECTIVES TABLE
01/08/2023 TODAY'S OBJECT LIST DATE
1 OBJECTIVE 1 1/8/2023
2 OBJECTIVE 2 1/8/2023
3 OBJECTIVE 3 1/8/2023
4
5=IFERROR(INDEX($B$3:$B$22,SMALL(IF(($C$3:$C$22=C26)*($C$3:$C$22<>""),ROW($B$3:$B$22)-2),ROW(A1))),"")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
- Detlef_Lewin
why is it a bad settup? it helps if you would give more details. I tried my best
My question (in one sentence) would be: how can I built a formula that returns rows from list that matches criteria (the dates) without blanks
thanks!!
