Can you use formulas in filters?
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!
Answers
-
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).
-
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.
-
You would use a formula in the checkbox column.
-
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...
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!