Can you use formulas in filters?

airwin

I am attempting to create a report that pulls from 11 sheets that are being manually updated weekly. I want the report to only show the most current week results from the 11 sheets. I am struggling to find a way for the report to always be pulling the most up to date information but I can only seem to enter a single value in the filter currently.

So my question is two parts

1. can you use a formula in the filter so i am not manually changing the filter each week?

2. if yes, what would my formula be? if no, is there a different way to pull the most current week info into the report?

Thanks in advance for the help!

Paul Newcome

You cannot use a formula in a report filter, but you can use a formula in a sheet to (for example) check a box for the row(s) you want pulled and then filter the report on the checkbox(es).

airwin

Thank you! Would I still have to manually check the boxes in the sheets prior to the report being pulled? I was hoping there was a way to have the report auto pull the current week information without having to go in and manually choose what information is being pulled into the report.

Paul Newcome

You would use a formula in the checkbox column.

Jeffmurr

On my sheets I created a column called weekday. The formula works off of the planned start date of a project plan and then allows me to create reports based on a specific week a task is starting....

=IFERROR(IF(WEEKDAY([Planned Start]@row) = 2, [Planned Start]@row, [Planned Start]@row - WEEKDAY([Planned Start]@row - 2)), "")

On the report, I use the following filters

1 - is in the last 5 days

2 - is in the future....

It works pretty slick to see active project tasks coming up within the next few weeks etc...