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.
I appreciate the precision you're seeking to employ in describing your situation. It's nevertheless difficult to actually picture exactly what you're up against based solely on the words. And although the cliche is that a picture is worth a thousand words, even if you were to post an image of your files, that wouldn't help much.
Is it possible for you to post a copy of the actual workbook (devoid of any personal or confidential info) so that we here in techcommunity can experience it first hand, and maybe offer the solution you're looking for?
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?