Forum Discussion
Filter - Drop down menu to show rows with cells with one or more values
The FILTER function is indeed expandable:
=FILTER(Table1[[Proj Name]:[Random3]],(Table1[StartWeek]<=RequestedWk)*(Table1[LastWeek]>=RequestedWk))
Gets the larger table view (as you can see in the attached). The key is in the red, where it now displays the starting column and the finishing.
It could also be sorted, if that were important. Just nest the FILTER formula within a SORT and specify the column on which the sort is to be based.
Oh, my. I still can't sort through all that, especially not without seeing the external files to which it refers.
That said, and without fully appreciating the complexities of what you're asking, I wonder if there isn't a way to simplify this--which is one of your apparent objectives, to make it intelligible for the users--by referring to single dates, or week numbers, rather than via references like "15.06.2020-21.06.2020"
That is, use the WEEKNUM function connected with either the start of the week or the end of the week as the basis for what you pull from the source data.
[I have another app where I helped somebody here through the techcommunity develop weekly status reports, and I did in in a way that allowed him to simply enter the date of Sunday at the start of any given week, that got converted by WEEKNUM which was then used to extract data from the source database. Now, we also added a formula in the source database that automatically generated the weeknumber in each row based on other data; i.e., no extra work for anybody once designed. So I don't know if something like that would help here.]
Thank you for your reply. I have considered using the WEEKNUM function, but it still doesnt solve the problem of an activity spanning across several weeks - And I am in need of a filtering function that shows all activities within a given week or time span, not exclusively those that only are active in week 1, 2 or 3 - Because activities will be active during several weeks..
I still think WEEKNUM could work for you. Your rows could show a week number value for the first week, the last (and the latter could increase as the project continues).
Filter based on being greater than or equal to the first week, less than or equal to the last.
The FILTER function could do that.
Or am I missing something?
Hmm.. I feel a bit stupid for not seeing how it could work. I think I get the formatting bit, I see how I can retrieve the necessary information, but the template need to work for several different projects with different start/end dates + Be simple enough for the users to just choose from a drop down menu or enter a spesific week or time period in a cell without having to customise the filter settings manually every time..
Would you be able to insert the solution in the excel example sheet I shared in order for me to see if I am missing something?
Thank you for your patience and understanding 🙂