I'm working on this Google Sheets where we show prices of accommodations. In the pivot you have the ability to filter on specific travel dates. But when you don't use the filter you see the average prices of all departure dates.
What I want to do is that users can fill in the departure date in cell L4. If they use this cell (choose a specific date from the dropdown) the results in the pivot will show the prices of that specific departure date. If this cell is empty the pivot has to show all the results.
For this I'm using 'start_date' as filter in the pivot where I chose 'filter by condition' 'date is' 'exact date' =L4. However, when L4 is empty the pivot is not showing any results but it has to show all results when this cell is empty. When L4 has a value (date) than it should only show the results of this date.
What the pivot does right now: When cell L4 contains a date it shows the results for that specific date. When L4 is empty the pivot shows nothing...
What the pivot should do: When cell L4 is empty it should not use the start_date filter and it should show all results.
If found the solution. The solution is to use 'Fliter by condition' in the Pivot Filter option and than choose Custom Formula is:
=IF(ISBLANK(L4), TRUE, Start_date = L4)