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.
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.
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.
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?
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.
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,
MagneI 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.