Forum Discussion
Filter - Drop down menu to show rows with cells with one or more values
I've made a template for a project plan (sheet 1) and exported the information to a project report (sheet 2).
In the project report I want to be able to filter project activities pertaining to a spesific week, but as activities can span across several weeks, I've made a macro in the project plan that let me choose several weeks from a list and insert it into the same cell.
When I try to filter by week in the report it is limited to picking activities that are exclusively pertaining to this, last, or next week, not activities that span across this and next week. It can be customised in the date intervall in the filter drop down menu, but this will be to complicated for the users..
Is there a way around this? I tried to remove the macro and insert the date (dd.mm.yyyy - dd.mm.yyyy), manually in the plan, but the same problem appears when filtering in the report.
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.
16 Replies
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.]
