Filter - Drop down menu to show rows with cells with one or more values

[mhar985]

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. 

[#2]

@mhar985 

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?

[#3]

@mathetes I can try to make a mock workbook to illustrate my problem 

[#4]

@mathetes 

[#5]

@mhar985 

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.]

[#6]

@mathetes 

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..  

[#7]

@mhar985 

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?

[#8]

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 :) 

[#9]

@mhar985 

THere's a simple example here in this revised (w/o VBA) version of your spreadsheet. Just to illustrate how FILTER could do it. I created a small table that contains start and end weeks and a project name. Then the following filter function will list projects (and any other data you choose to add) that meet the single week.

=FILTER(Table1[Proj Name],(Table1[StartWeek]<=RequestedWk)*(Table1[LastWeek]>=RequestedWk))

[#10]

Hi @mathetes , 

At first look this seems to do exactly what I would want it to do. But will it work in a template where activities do not have predetermined names or dates, but will be randomly inserted in the plan as the project manager goes along? 

In other words, will the project manager be able to expand the number of activities and dates in sheet 1 (project plan) and filter his results by a week to week basis in the project report (sheet 2)?

As you understand whether the cells containing dates in sheet 1 (the plan) accomodates more predetermined dates into one cell (VBA), or is a freely inserted by the project manager, is not that important. This was done originally because I thought the filtering in sheet 2 (the report) would be easier this way. 

[#11]

Expanding on this: Project activities could be "remember to give Lise the keys". Then the whole row containing this activity needs to be exported to the plan

[#12]

@mhar985 

I will confess to having a difficult time picturing what you're describing. That said, I am assuming (as is Excel) that you (and your users) are using the Excel Table format to represent a coherent and well-designed database.

This pretty much precludes random entries (which is what "remember to give Lise the keys" sounds like); i.e., I wouldn't  think you want this Excel workbook used as the daily "To-Do" list.

Not that it's impossible to use it as a To-Do list. It is entirely possible.

But this very conversation is telling me that what you (and your users) need to do first is sit down and give thought to what you're designing. Don't make the mistake (or let them make the mistake) of using Excel as a semi-automated piece of paper, a piece of paper that happens to come with rows and columns and even the ability to do some magic with filtering and adding and multiplying and extrapolating.... [and believe me, there's plenty of precedent for bringing that mindset to Excel]

Or, at the very least, it's what I would insist on doing before going any further. You've got the tools to do the filtering you were asking about; they may take some tinkering to use dates rather than week numbers, and so forth....but seriously, sit down and assemble a set of descriptions of

• INPUT: data that is needed in the course of the project management
• OUTPUT: reports/summaries that are needed

Then refine how the INPUT in particular should be entered; what's truly needed for project management, what really is trivial and could be done via some other app (or piece of paper), how that input data can be consistently entered

THEN start looking at methods to produce the reports.

[#13]

@mathetes Thank you for all your patience and willingness to help me in this matter.

The thorough process of deciding on what to include in the plan+report template is a preset taken from the methodology of these projects. As for the combination of random and preset or numerical information, I tried to examplify this in the excel file, but let me try to state it in a different matter using the tables below: 

Project Plan (sheet 1)

Project activity	Activity date	Other information	Other information	Other information
Random	Date intervall	Random	Random	Random
Random	Date intervall	Random	Random	Random
Random	Date intervall	Random	Random	Random

Project Report (sheet 2, exported from sheet 1)

Project activity	Activity date selection	Status	Other information	Other information
Random	Date intervall	Fill inn status	Random	Random
Random	Date intervall	Fill inn status	Random	Random
Random	Date intervall	Fill inn status	Random	Random

In the report the only new information is the status field - Everything else from the plan is exported (although some coloumns are hidden). To filter only for activities to be focused on in a given week we want users to be able to easily filter activities (excel rows) based on weeks.

The problem arrise when using the built in filter option it will only draw information from what is

a) already typed into the cells in the coloumn, and not predetermined intervalls or 

b) when using intervalls (like a week number) only options that only coincides with the chosen week, not those that span across that week - and others at the same time

Your solution solved the filter issue, but I wasn't able to use it since the name (randomly given activity names) also needed to be predetermined in the same formula. 

I don't expect you to use more time one this for now, you have been more than generous with your time. Thank you for all your help. 

Best, 
Magne 

[#14]

@mhar985 

From a sheer design point of view, I would still recommend changing that column (mis-)labeled "Activity Date" into two (or even, depending on how you're going to use it, four) columns. It actually is not a "Date" (singular), but contains "Dates" (plural), being a span from start to finish, or expected finish, dates. That means it is a text field in Excel, which means that to use it as the basis for filtering you need to use elaborate formulas to break it into dates (entirely possible, but why make it difficult?!).

Have a minimum of two columns, "Start Date" and "End Date" and use the FILTER function formula I showed you based on that. If it really is WEEKs you want to show, then I'd add hidden "helper" columns that convert the "Start Date" to a "Start WEEKNUM" and "End Date" to "End WEEKNUM" and use the filter as I'd shown you.... Again, those can be hidden. And if you want to have the report show a Date Interval in a single text field, you can do that by converting the dates to text and then concatenating them....

I would probably also have the "Status" data filled in on the Project Plan sheet if it were my design. But I don't understand your work situation.... I'm viewing a Report as simply that, a Report, an output.

[#15]

@mathetes thank you - Yes, there is no need to make it difficult, two columns; start + end date would work fine. The filter function need to apply weeks, because the method envision weekly reports. The filtering is as such, just there to force them to only report on issues tied to a spesific week, and not everything else in the plan. 

We could just add a column "status report" in the project plan and work in the same sheet, but the plan is made at one stage and the reporting in a later, and there is a cut of date. We could just make a copy of sheet 1 at the cut of date, but this is expecting to much of the project managers. So the template need to automate that process and hide the "only project plan relevant" information in the report sheet - and filter project activites relevant for a spesific week. 

Would it be able to expand your formula to include export of the whole row in sheet 1 being active in the relevant week?  

[#16 · Accepted solution]

@mhar985 

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.

[#17]

@mathetes Thank you!